sql - Pass temp table to EXEC sp_executesql -

how can pass temp table (@table) exec sp_executesql @query

    set @query = 'select gsname, ' + @cols + '          (             select gsname, [thour], numoftransactions             @table        ) x         pivot          (              max([numoftransactions])             [thour] in (' + @cols + ')         ) p ' 

what have here not temporary table, table-valued parameter.

table-valued parameters declared using user-defined table types. can use table-valued parameters send multiple rows of data transact-sql statement or routine, such stored procedure or function, without creating temporary table or many parameters.

sp_executesql support table-valued parameters, must of declared type.

-- so, first must declare user-defined table type  create type udtyb_test table(gsname nvarchar(100), thour time, numoftransactions int); go  -- can create table-valued parameter declare @table udtyb_test;  -- , store there data insert @table (gsname, thour, numoftransactions) values ('sample', sysutcdatetime(), 1);  -- reference select * @table;  -- pass variable sp_executesql need parameters definition declare @parmdefinition nvarchar(500) = n'@table udtyb_test readonly'; -- please note: table-valued parameter must readonly  -- here use simplified query demonstration declare @query nvarchar(500) = 'select * @table';  -- , result should identical reference above execute sp_executesql @query, @parmdefinition, @table = @table;  -- user-defined table type cleanup drop type udtyb_test; go 

in practical cases easier use temporary table:

create table #table (gsname nvarchar(100), thour time, numoftransactions int); insert #table (gsname, thour, numoftransactions) values ('sample', sysutcdatetime(), 1); select * #table; declare @query nvarchar(500) = 'select * #table'; execute sp_executesql @query; drop table #table;