Pass a Table Variable to Sp_Executesql

using Table variable with sp_executesql

Here's an example of how to pass a table-valued parameter to sp_executesql. The variable has to be passed readonly:

if exists (select * from sys.types where name = 'TestTableType')
    drop type TestTableType

create type TestTableType as table (id int)
go
declare @t TestTableType
insert @t select 6*7

exec sp_executesql N'select * from @var', N'@var TestTableType readonly', @t

This prints the Answer to the Ultimate Question of Life, the Universe, and Everything.

How to pass a table variable using sp_executesql

Figured out the problem.

Apparently sp_executesql expects the parameter definition for a table to be of a table type (see this answer for an example: https://stackoverflow.com/a/4264553/21539).

An easier way to solve this problem was to insert the variables names directly into the SQLStatement string as follows:

DECLARE @SQLString NVARCHAR(MAX), 
        @TableName NVARCHAR(MAX);

SET @TableName  = N'[dbo].[MyTable]';

SET @SQLString = N'SELECT * FROM ' + @TableName + ';';

SET @ParamDefinition = N'@_TableName NVARCHAR(max);

EXEC sp_executesql @SQLString;

How to use sp_execute passing table parameter

Typically you don't pass a table variable to execute SQL with sp_executesql. You make a statement up out of text and execute that. Like so:

IF OBJECT_ID('tempdb..#People') IS NOT NULL 
    DROP TABLE tempdb..#People

CREATE TABLE #People (PersonId INT IDENTITY, PersonName VARCHAR(128));

INSERT INTO #People (PersonName) VALUES ('Brett'), ('John'), ('Mark'), ('Shawn'), ('Ryan'), ('Kevin');

DECLARE @SQL NVARCHAR(Max) = 'Select * from #People'

EXEC sp_executesql @Sql

UPDATE 1-27-17

IF OBJECT_ID('tempdb..#People') IS NOT NULL DROP TABLE tempdb..#People
IF OBJECT_ID('tempdb..#People2') IS NOT NULL DROP TABLE tempdb..#People2

CREATE TABLE #People (PersonId INT IDENTITY, PersonName VARCHAR(128));
CREATE TABLE #People2 (PersonId INT IDENTITY(7,1), PersonName VARCHAR(128));

SET NOCOUNT ON;

INSERT INTO #People (PersonName) VALUES ('Brett'), ('John'), ('Mark'), ('Shawn'), ('Ryan'), ('Kevin');
INSERT INTO #People2 (PersonName) VALUES ('Emily'), ('Beth'), ('Jane'), ('Hannah');

--I. getting an output for a single output variable dynamically
--Say I just want to get Ryan by his Id dynamically and output it
--I need to define one or many parameters OUTSIDE the scope of the Dynamic Sql
DECLARE @Output VARCHAR(8)
DECLARE @PersonId INT = 5

--I then need to associate the parameters as an array, for the purposes of explanation I will use DIFFERENT NAMES you may use the same
DECLARE @ParmDefinition NVARCHAR(500) = N'@PersonIdInside Int, @OutputInside varchar(8) OUTPUT'

--I then use the names ABOVE in the dynamic sql
DECLARE @SQL NVARCHAR(Max) = N'Select @OutputInside = PersonName from #People Where PersonId = @PersonIdInside'

-- I then do the following AFTER the sp_executesql 1. The Dynamic sql nvarchar, 2. The params nvarchar 3. one or many variables and how they associate
EXEC sp_executesql @Sql, @ParmDefinition, @PersonIdInside = @PersonId, @OutputInside = @Output OUTPUT

-- I have an output so now it should show what I want
SELECT @Output

-- II. getting a result set dymamically to another record set or table OUTSIDE the scope of the internal
-- Create another table, I use a #table for example purposes 
IF OBJECT_ID('tempdb..#Output') IS NOT NULL DROP TABLE tempdb..#Output

CREATE TABLE #Output (PersonId INT IDENTITY, PersonName VARCHAR(8))

--Get a truncated list for an 'in' statement later of person Id's in a variable
DECLARE @People NVarchar(32) = N'1, 5, 10'

--I then use the @People ABOVE in the dynamic sql putting it together and then do an 'insert statement first'
DECLARE @SQL2 NVARCHAR(Max) = N'Insert Into #Output SELECT PersonName FROM (SELECT * FROM #People UNION SELECT * FROM #People2) as x Where PersonId in (' + @People + ')'

--execute yields nothing
EXEC sp_executesql @Sql2

-- or does it?
Select *
From #Output

-- !!! WARNING !!!
-- With dynamic sql you cannot nest multiple dynamic sql statements inside of procs.  EG: Proc1 cannot call Proc2 and both of them have dynamic sql in them.  Engine limitation.

Pass temp table to EXEC sp_executesql

What you have here is not Temporary Table, but a Table-Valued Parameter.

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

In most practical cases it is much easier to use a temporary table:

Create Table #table (GSName nvarchar(100), THour time, NumOfTransactions int);
Insert Into #table (GSName, THour, NumOfTransactions) Values ('Sample', SYSUTCDATETIME(), 1);
Select * From #table;
DECLARE @query nvarchar(500) = 'SELECT * FROM #table';
EXECUTE sp_executesql @query;
Drop Table #table;

How to use table variable in dynamic sql? OR create temporary table from user defined table type?

I can think of the following workarounds to solve this using your UDTT:

1. Declare the UDTT variable within your dynamic script and then you can as well retrieve results from there:

    EXECUTE SP_EXECUTESQL 
        N'
        DECLARE @dynvariable [UDTT];
        insert @dynvariable values (1);
        select * from @dynvariable';

2. Pass the UDTT variable to the SP_EXECUTESQL, but then it is readonly, meaning you can only select within the dynamic script:

DECLARE @variable [UDTT];
insert @variable values (1);

EXECUTE SP_EXECUTESQL 
    N'select * from @dynvariable', 
    N'@dynvariable [UDTT] READONLY', 
    @dynvariable=@variable;   

3. I think it's not possible to 'create a temp table from UDTT' so your approach would be to dynamically create the temp table using system information for your UDTT (columns, types, etc.).

4. Reading that you want to have a "dynamic" pivot code, the most appropriate would be to dynamically generate the pivot statement based on the columns info and values of the target table.

How to use table variable in a dynamic sql statement?

Your EXEC executes in a different context, therefore it is not aware of any variables that have been declared in your original context. You should be able to use a temp table instead of a table variable as shown in the simple demo below.

create table #t (id int)

declare @value nchar(1)
set @value = N'1'

declare @sql nvarchar(max)
set @sql = N'insert into #t (id) values (' + @value + N')'

exec (@sql)

select * from #t

drop table #t

---

Related Topics