How to store Dynamic SQL Output data into a Temporary table in SQL Server

As you know developers life is not so easy. We know that if we want to write a complex SQL then we like to break this SQL in different parts. To do that we use either view or temporary table to break down the complex SQL which will more readable & easy to modify. Here I will show you how we can store Dynamic SQL OUTPUT into temporary table. So that you can use this temporary table with another table to make SQL JOINS like Inner Join, Left Join & Right Join also you can then apply SET operation. One of the examples is given below:

ALTER Procedure ParamToDynamicSQL(@bActive bit)

ID bigint,
CategoryID bigint,
Title varchar(500),
Published datetime,
ModifedDate datetime,
Active bit,
TotalView bigint

DECLARE @ParameterList NVARCHAR(1000)

SET @ParameterList = '@bActive bit'
SET @sSQL='SELECT * FROM Article WHERE Active=@bActive'

INSERT @Article
EXEC SP_EXECUTESQL @sSQL,@ParameterList,@bActive=@bActive

SELECT * FROM @Article


Now you can invoke the above SP like below:

EXEC ParamToDynamicSQL 1


This is all about Dynamic SQL. Hope now you can write runtime Dynamic SQL in SQL Server Stored Procedure (SP) to meet the client requirements.

Posted in Database, SQL Server

Leave a Reply

Your email address will not be published. Required fields are marked *