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

Question

I am using SQL Sever 2016 and I have created user-defined table-type as below:

CREATE TYPE [dbo].[UDTT_Items] AS TABLE(    
    [ItemId] int identity(1, 1),
    [ItemCode] [varchar](10) NULL,
    [ItemName] [varchar](255) NULL, 
    [StockQty] decimal(18,3 ) NULL, 
    PRIMARY KEY CLUSTERED 
(
    [ItemId] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO

In my stored procedure I can create table variable like this:

declare @tblItems UDTT_Items

I can insert data in this table variable and can make select queries.

select * from @tblItems

The problem I faced when I need to put this table in dynamic sql. For example, if I try to run the above select statement from execute caluse:

EXECUTE SP_EXECUTESQL N'select * from @tblItems'

It gives me error message:

Must declare the table variable "@tblItems".

I tried to use temporary table variabe (with #) inside dynamic sql, and it works fine, but I dont know if I can create temporary table with already user-defined-table-type. I need something like this:

create #tblItems UDTT_Items 

But it also does not work.

Can anybody suggest how to make any work around this issue, either by using table variable in dynamic sql, or creating temp table from user-defined-table-type?

Answer 1

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.