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)
AS
BEGIN

DECLARE @Article TABLE
(
ID bigint,
CategoryID bigint,
Title varchar(500),
Published datetime,
ModifedDate datetime,
Active bit,
TotalView bigint
)

DECLARE @sSQL NVARCHAR(MAX)
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

END

Now you can invoke the above SP like below:

EXEC ParamToDynamicSQL 1

TemporaryTable

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.

Leave a Reply

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

AlphaOmega Captcha Classica  –  Enter Security Code