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