Write & Execute Dynamic SQL in SQL Server Stored Procedure

Please visit my new Web Site https://coderstechzone.com

Most often we have to Create Dynamic SQL for different conditions to bring back results in our Asp.net applications. If we make the SQL in page code behind then its simple & easy. But think when one need to reuse the query with another set of values then what he can do? Copy the code block & paste it into the newly created aspx page? No here i will try to give you examples on How To Create a Dynamic SQL on the fly in SQL Server SP & reuse it. So lets start with an example:

A simple requirement:
Let you want to pass a table name into a Stored Procedure then collect all data to bind with a GridView. So our dynamic sql should be:

CREATE PROCEDURE GetAllRows @topN int,@tblName varchar(200)
AS
SELECT TOP @topN * FROM @tblName

Uffs you will get the below error:
Msg 102, Level 15, State 1, Procedure GetAllRows, Line 3
Incorrect syntax near '@topN'.
OR
Msg 1087, Level 15, State 2, Procedure GetAllRowss, Line 3
Must declare the table variable "@tblName".

This is the limitation of Dynamic SQL. Don't worry SQL Server provide us two different ways to built a dynamic SQL Statement. These ways are as follows:
1. EXEC()
2. sp_executesql()

Using EXEC():
EXEC takes only one parameter which will be your Dynamic SQL. Its easy to use. If you want to pass few parameters into stroed procedure & then generate Dynamic SQL then its your easy choice. So we can rewrite the previous SP in the following way:

CREATE PROCEDURE GetAllRows @topN int,@tblName varchar(200)
AS
DECLARE @sSQL nvarchar(MAX)
SET @sSQL='SELECT TOP '+CONVERT(varchar(MAX),@topN)+' * FROM '+@tblName
EXEC(@sSQL)

The below command will invoke your SP:

EXEC GetAllRows 10,'AnyTableName'

Now you will get 10 rows from your provided table name.

Using sp_executesql():
The sp_executesql() is a built in System Stored Procedure, an alternative and most flexible upgradation of EXEC(). By using sp_executesql() you will get some advantages like passing parameters into the dynamic sql which i will write later in my another post. Here i will show you a simple example on using sp_executesql():

CREATE PROCEDURE GetAllRows @topN int,@tblName varchar(200)
AS
BEGIN
DECLARE @sSQL nvarchar(MAX)
SET @sSQL='SELECT TOP '+CONVERT(varchar(MAX),@topN)+' * FROM '+@tblName
execute sp_executesql @sSQL
END

The below command will invoke your SP:

EXEC GetAllRows 10,'AnyTableName'

I will suggest if & only if badly needed then use dynamic sql since it has a security hole as well as performence issues may arises. The other thing is difficult to debug since you are creating a string which will execute after invoke. So becareful before going live.

This post is basically an introductory article on "How to write Dynamic SQL". I will try to give more explanation on next articles. Untill then happy programming.

CLICK HERE TO READ PART II.