sql - Pass temp table to EXEC sp_executesql - Stack Overflow

sql-pass-temp-table-to-exec-sp_execute.sql

/*What you have here is not Temporary Table, but a Table-Valued Parameter.
--Url: https://stackoverflow.com/questions/38823690/pass-temp-table-to-exec-sp-executesql#:~:text=You%20can%20use%20table-valued%20parameters%20to%20send%20multiple,parameters%2C%20but%20they%20must%20be%20of%20declared%20type.
Table-valued parameters are declared by using user-defined table types. You can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.

sp_executesql does support table-valued parameters, but they must be of declared type.
*/
-- So, first we must declare User-Defined Table Type 
CREATE TYPE udtYB_Test AS TABLE(GSName nvarchar(100), THour time, NumOfTransactions int);
GO

-- Now we can create Table-Valued Parameter
Declare @table udtYB_Test;

-- And store there some data
Insert Into @table (GSName, THour, NumOfTransactions)
Values ('Sample', SYSUTCDATETIME(), 1);

-- Just for the reference
Select * From @table;

-- To pass variable to sp_executesql we need parameters definition
DECLARE @ParmDefinition nvarchar(500) = N'@table udtYB_Test READONLY';
-- Please note: table-valued parameter must be READONLY

-- Here I use simplified query for demonstration only
DECLARE @query nvarchar(500) = 'SELECT * FROM @table';

-- and the result should be identical to the reference above
EXECUTE sp_executesql @query, @ParmDefinition, @table = @table;

-- User-Defined Table Type cleanup
DROP TYPE udtYB_Test;
GO