How to get Dynamic SQL Select query to an table variable?
It is very often easy to manage complex search, paging and sorting using dynamic SQL feature provided in SQL Server 2008. Very often when you are executing a dynamic query, there would be situations where we may need to manupulate the result of the dynamic SQL.
SQL Server provides a mechanisam to do this for a dymanic SQL which performs a SELECT operation through the following syntax:
INSERT INTO @table_variable EXECUTE (query)
For example, for getting the second page after applying the condition, the query could be written as follows:
DECLARE@PageSize int = 1000
DECLARE @table TABLE(rowId int IDENTITY(1,1),id int, value varchar(500))
DECLARE @condition nvarchar(max),@Query nvarchar(max)
set @condition = N' where id > 1000 order by id'
SET @query = 'SELECT TOP ' + cast(@PageSize as varchar) + ' Id, Name FROM Customer ' + @condition
INSERT INTO @table(id,value) EXECUTE(@query)
SELECT * FROM @table
Hope this helps you.
SQL Server provides a mechanisam to do this for a dymanic SQL which performs a SELECT operation through the following syntax:
INSERT INTO @table_variable EXECUTE (query)
For example, for getting the second page after applying the condition, the query could be written as follows:
DECLARE@PageSize int = 1000
DECLARE @table TABLE(rowId int IDENTITY(1,1),id int, value varchar(500))
DECLARE @condition nvarchar(max),@Query nvarchar(max)
set @condition = N' where id > 1000 order by id'
SET @query = 'SELECT TOP ' + cast(@PageSize as varchar) + ' Id, Name FROM Customer ' + @condition
INSERT INTO @table(id,value) EXECUTE(@query)
SELECT * FROM @table
Hope this helps you.
Comments