August 27, 2018

How to use table variable in dynamic sql?

I came across a scenario while working with user-defined-table-types where I was need to query from table variable using dynamic SQL. I feel it worth sharing here in case someone might need in future.

I have created a user-defined table-type:

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 declared a table variable for this UDTT:

declare @tblItems UDTT_Items

Here is how we normally use select statement on table variable.

select * from @tblItems

The problem comes when I tried to put this table variable in dynamic SQL. For example, if I try to run the above select statement from execute clause:

EXECUTE SP_EXECUTESQL N'select * from @tblItems'

It gives me the error message:

Must declare the table variable "@tblItems".

Fortuntely there is work around available by EXECUTE SP_EXECUTESQL. You can pass the table variable as a read-only parameter to dynamic SQL. So the following dynamic sql will work and run the given query as exptected.

DECLARE @SQL NVARCHAR(MAX);
SET @SQL ='SELECT * FROM @tblItems;';
EXECUTE SP_EXECUTESQL @SQL,N'@tblItems UDTT_Items READONLY',@tblItems;

Here I put my query in @SQL variable, and when calling EXECUTE SP_EXECUTESQL, we have to first define our parameters that need to be passed as second argument, and the actual table variable(i.e. parameter value) in third argument.

No comments:

Post a Comment