How to Use Table Variable in a Dynamic SQL Statement

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 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 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;

How to insert into a table variable with a dynamic query?

This is simple minimal example. You can use INSERT EXEC statement. The key is to have table variable declared inside and outside dynamic query. At the end of dynamic query just select from table variable and insert resultset into outside table variable:

DECLARE @t TABLE ( id INT ) 

DECLARE @q NVARCHAR(MAX) = 'declare @t table(id int) 
                            insert into @t values(1),(2) 
                            select * from @t'

INSERT INTO @t
EXEC(@q)

SELECT * FROM @t

How to use a table variable within EXECUTE command?

Simply, you can't.

This is not possible, cause the table variable is isolated from the scope of the Dynamic SQL. You can only use temporary and normal tables.

Or declare it withing the Dynamic SQL as

EXECUTE sp_executesql N'DECLARE @MyTABLE TABLE (x int); 
                        INSERT INTO @MyTable VALUES (1);
                        SELECT * FROM @MyTable';

How to use Table -Valued Parameter with Dynamic qry

Inside dynamic query, you cannot use table variables declared outside. Use temp table instead. Also you have complicated it little too much, here is a cleaner version

DECLARE @SyncFlag AS NVARCHAR(1) ='S'
DECLARE @SelectCommand AS NVARCHAR(1200)

CREATE TABLE #Details_N(Pk INT)

INSERT INTO #Details_N(Pk)
SELECT PK
FROM   @Details

SET @SelectCommand = 'Update A ' + ' set A.Sync_Flag = @SyncFlag 
                        From '+ Quotename(@tblName) + ' A 
                        inner join #Details_N B '+ 'on A.PK =' + 'B.PK'

EXEC Sp_executesql
  @SelectCommand,
  N'@SyncFlag NVARCHAR(1)',
  @SyncFlag 

Start using INNER JOIN syntax, old style comma separated join is deprecated

---

Related Topics