How to pass SP parameters into Dynamic SQL

Please visit my new Web Site WWW.Codedisplay.com

In my first article I wrote An Introduction on creating Dynamic SQL in SQL Server Stored Procedure (SP). In this article I will try to write a bit advance Dynamic SQL. Here i will show you How one can transfer or pass parameter into Dynamic SQL query & get scalar value as output parameter or can Store output data into a temporary table.

CLICK HERE To read "How can invoke/read SQL Server SP from ASP.net ASPX page".

Focus Area:
1. How one can pass an Input Parameter to Dynamic SQL
2. How one can pass OUTPUT Parameter to Dynamic SQL
3. How one can store Dynamic SQL data into a Temporary table

To do that first create a table Article like below:

ID - bigint - Unchecked
CategoryID - bigint - Unchecked
Title - varchar(500) - Unchecked
Published - datetime - Unchecked
ModifedDate - datetime - Checked
Active - bit - Unchecked
TotalView - bigint - Checked

Then insert below rows into Article table:

INSERT INTO Article
VALUES(1,1,'How to start AJAX','1/1/2009 12:00:00 AM','1/1/2009 12:00:00 AM',1,1005)

INSERT INTO Article
VALUES(2,2,'How to write Dynamic SQL','1/1/2009 12:00:00 AM','1/1/2009 12:00:00 AM',1,1005)

INSERT INTO Article
VALUES(3,1,'Pass parameters','1/1/2009 12:00:00 AM','1/1/2009 12:00:00 AM',1,1005)

INSERT INTO Article
VALUES(4,1,'Advance SQL Sored Procedure','1/1/2009 12:00:00 AM','1/1/2009 12:00:00 AM',0,1005)

INSERT INTO Article
VALUES(5,1,'Advance JQUERY Articles','1/1/2009 12:00:00 AM','1/1/2009 12:00:00 AM',0,1005)

Now our environment is ready for testing. Let our requirement is list all articles based on Active or not. So need to write a stored procedure which will take one parameter for Active or inactive & then we need to pass dynamic sql result into a temprary table.

So first write a Stored Procedure(SP) in SQL Server like below:

CREATE Procedure ParamToDynamicSQL(@bActive bit)
AS
BEGIN

DECLARE @sSQL NVARCHAR(MAX)
SET @sSQL='SELECT * FROM Article WHERE Active=@bActive'
EXEC(@sSQL)

END

The SP was created. Now run the Above SP by invoking below code:

EXEC ParamToDynamicSQL 1

UFFS i found an error. The error is:
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@bActive".

Yes you can not use parameters within the Dynamic SQL directly in a SP. That’s why we need to use the power of SP_EXECUTESQL built in method to pass parameters into dynamic sql instead of EXEC or EXECUTE method. Which i have discussed in my first article on "An Introduction on creating Dynamic SQL in SQL Server Stored Procedure(SP)".

How one can pass an Input Parameter to Dynamic SQL:
To do that first declare a NVARCHAR type variable to store all parameters & then pass it through SP_EXECUTESQL method like below:

ALTER Procedure ParamToDynamicSQL(@bActive bit)
AS
BEGIN

DECLARE @sSQL NVARCHAR(MAX)
DECLARE @ParameterList NVARCHAR(1000)

SET @ParameterList = '@bActive bit'

SET @sSQL='SELECT * FROM Article WHERE Active=@bActive'
EXEC SP_EXECUTESQL @sSQL,@ParameterList,@bActive=@bActive

END

Now run the above SP by invoking the below command:

EXEC ParamToDynamicSQL 1

Fig: OUTPUT

How one can pass OUTPUT Parameter to Dynamic SQL:
So I think initial work around is done. Now I will try to show you how we can use OUTPUT parameter in Dynamic SQL. Lets now our requirement is to show number of active articles. So we need to modify our previous SP like below:

ALTER Procedure ParamToDynamicSQL(@bActive bit,@TotalCount int OUTPUT)
AS
BEGIN

DECLARE @sSQL NVARCHAR(MAX)
DECLARE @ParameterList NVARCHAR(1000)

SET @ParameterList = '@bActive bit,@TotalCount int OUTPUT'

SET @sSQL='SELECT @TotalCount=COUNT(*) FROM Article WHERE Active=@bActive'
EXEC SP_EXECUTESQL @sSQL,@ParameterList,@bActive=@bActive,@TotalCount=@TotalCount OUTPUT

END

And you can invoke the Stored procedure in the following way:

Declare @TotalCount int
EXEC ParamToDynamicSQL 1,@TotalCount OUTPUT
print @TotalCount

Fig: OUTPUT

Ok now I hope you can pass parameter value into Dynamic SQL as well as can retrieve OUTPUT parameter value from Dynamic SQL.

How one can store Dynamic SQL data into a Temporary table:
As you know developers life is not so easy. The above techniques may not ease your life. 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

Fig: OUTPUT

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.