Using Table Variable with 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.

Table variable in sp_executesql parameter list

You need to create a named type that your table variable will use:

create type dbo.myTableType as table (id int)

Then you can use it as a typed argument to sp_executesql:

declare @m dbo.myTableType;
insert @m values (1), (2)

exec sp_executesql 
    N'select 99 where 1 in (select id from @m)', 
    N'@m dbo.myTableType readonly', 
    @m

If you don't want to create a new type, you can use a #temp table:

declare @t table(id int);
insert @t values (1), (2);
select * into #t from @t;

exec sp_executesql N'select 99 where 1 in (select id from #t)';

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.

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

How to use Table Variable in Dynamic Query

Try This :

CREATE TYPE IntegerTableType AS TABLE (ID INT);
go

DECLARE @TempVehicles IntegerTableType; 

INSERT  @TempVehicles
values (1);

DECLARE @SQL NVARCHAR(MAX);
SET @SQL ='SELECT * 
           FROM @TempVehicles;';

EXECUTE SP_EXECUTESQL @SQL,N'@TempVehicles IntegerTableType READONLY',
@TempVehicles;

sp_executesql and table output

Resolved, thanks to all for tips:

DECLARE @DBName varchar(255) 
DECLARE @q varchar(max) 
CREATE table #tempTable(myParam1 int, -- other params)

SET @DBName = 'my_db_name'
SET @q = 'insert into #tempTable exec ['+@DBName+'].[dbo].[my_procedure]'
EXEC(@q)

SELECT * FROM #tempTable
drop table #tempTable

---

Related Topics