1

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?

M_Idrees
  • 2,080
  • 2
  • 23
  • 53
  • It seems like you already have a code that you've tried which yields an error. Please include it. – Kamil Gosciminski Aug 27 '18 at 08:26
  • Yes. This line "EXECUTE SP_EXECUTESQL N'select * from @tblItems'" giving error, now added in question. – M_Idrees Aug 27 '18 at 08:28
  • 1
    you have to declare your variables *within* the dynamic script itself – Vladislav Aug 27 '18 at 08:31
  • Yes that may be a solution but I have lot of things to do with this table, like first insert from 3 different queries, then execute 2 update queries, and these queries are not very simpler. Moving these to dynamic sql would increase code complexity and chances for errors. – M_Idrees Aug 27 '18 at 08:34
  • Can you explain why you need to use dynamic sql for this? The example gives no clue. Bottom line is that the table variables are local scope, which you are stepping out of with your dynamic sql. – TomC Aug 27 '18 at 08:37
  • I need to use dynamic sql because at the end of stored-procedure I need to pivot the output. and while pivoting the table, it may have different number of columns which can not be fixed. – M_Idrees Aug 27 '18 at 08:40
  • For simplicity, I just put simple table in question, but in my scenario, there may be 5/10/15 any number of rows could be returned which then need to be pivot into columns. – M_Idrees Aug 27 '18 at 08:42

1 Answers1

2

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.

Vladislav
  • 2,772
  • 1
  • 23
  • 42