Dynamic SQL in a Stored Procedure - part 1

Hi, welcome again... :)
I'll cover this topic under two parts

• Dynamic SQL in a Stored Procedure - part I
• Dynamic SQL in a Stored Procedure - part II

With part I, I'll explain the basic concepts behind the dynamic sql queries and in second post I'll go more deep covering performance, security etc... Sometimes we face situation where, hard coded sql queries are not a solution. In case such a situation we need to create dynamic sql queries on the fly & execute them.

So what is a Dynamic SQL query?
With Dynamic SQL you can write one or more sql statement(s) & put it inside a string variable. We can write dynamic sql queries in both application side and database server side. Handling application side is pretty much easy. But when it comes to write dynamic queries in a stored procedure, there are many concerns.


Why use Dynamic SQL Query?
As developers we might face situations, where we have to deal with database in a different manner, for same task. For a example consider you have a simple application (See below image), that enable user to find a student by providing his ID or Name or both of them. Now think, how would you query the database in order to find out student with given parameters (think about what would be the where clause in your query). In such scenarios Dynamic Query comes very handy.

Methods of Implementing
There are two ways of executing dynamic sql queries.

1.  SP_EXECUTESQL
2.  EXECUTE()

Let's see how to write dynamic sql query for above scenario. Here I am using first method which has many advantages over execute method (In a stored procedure). If I have to made a choice between these two methods I'll definetly go for first one(in most cases :) ). Advantages of sp_executesql over EXECUTE() explained in part II.

1. Using sp_executesql system stored procedure

USE [TestDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_GetStudent]
 
 /*PARAMETERS PASSED FROM APPLICATION SIDE*/
 @ID INT,
 @NAME NVARCHAR(50) 

AS
BEGIN
 
 SET NOCOUNT ON;

 DECLARE @SQL_QUERY NVARCHAR(MAX) ;/* VARIABLE TO HOLD SQL QUERY STRING*/
 DECLARE @WHERE_CLAUSE NVARCHAR(MAX);/*VARIABLE TO HOLD WHERE PART OF THE QUERY*/
 DECLARE @PARAMETER_DEFINITION NVARCHAR(MAX); /* PARAMETER TO HOLD, DEFINITIONS OF USED PARAMETERS INSIDE THE QUERY STRING */
 
 SET @PARAMETER_DEFINITION = '@ID INT,@NAME NVARCHAR(50)' 
 SET @WHERE_CLAUSE = ' '
 SET @SQL_QUERY = 'SELECT * FROM TestDB.dbo.tblStudent WHERE (1=1)'

 IF @ID IS NOT NULL
 BEGIN 
  SET @WHERE_CLAUSE = @WHERE_CLAUSE + ' AND ID = @ID'
 END

 IF @NAME IS NOT NULL
 BEGIN
  SET @WHERE_CLAUSE = @WHERE_CLAUSE + ' AND Name = @NAME'
 END

 SET @SQL_QUERY =  @SQL_QUERY + @WHERE_CLAUSE
 PRINT @SQL_QUERY
 EXEC SP_EXECUTESQL @SQL_QUERY, @PARAMETER_DEFINITION, @ID, @NAME
END

Query string will be assigned to @SQL_QUERY variable. That must be declared as a nvarchar variable. Based on the values passed to parameters, where clause get created. Finally sp_executesql
should be execute by passing query string, parameter definition and list of parameters used in query string. @PARAMETER_DEFINITION variable used to hold the definition of parameters. Make sure you include all parameters use in sql string, into @PARAMETER_DEFINITION. This variable must be type of nvarchar too.

Both EXECUTE & EXEC keywords are equivalent here. You can use either one of them. I usually go with the short version. I've used PRINT command here just for check the query. You can omit that line if you do not want it.

When you'll need Dynamic SQL Queries
Dynamic queries very helpful when you need to use following items in variables in a query,(assume following items in a query get changed time to time based on a given scenario. So you can't hard code them).

• Server name
• Table name 
• Column name

This is because sql server doesn't allow user to replace above parts with a variable. Also if you have different kind of filtering scenario like explained above it will be a good choice to go for dynamic sql queries.

Hope now you have a good understanding of basics of dynamic sql queries. See you again with the Part II of this post...