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...

Read more

How To Write a SQL Cursor

All programming languages have it's own way of handling iteration operations. In SQL server one way to do this is using a 'CURSOR'.

Although there are some other sql iterative solutions like while loop, here we focus only on T-sql cursors.

When writing sql queries we often need to loop through different types of records sets, in order to achieve certain tasks. It's very common.

Personally I always forget cursor syntax & when I need to use it what I'm doing is, just get it copied from somewhere else. :D Let's look into cursors in detail.

How it operates

Cursor is a database object & it manipulates given record set row by row basis. Which means it take first record, perform what it needs to be done & move to next record and do whatever the thing mentioned. It goes like this until the end of the record set. 

In this example I'm trying to read some data from few tables & insert them into a temp table called #TEMP_TABLE. First of all go to following link to download complete Sql query for creating required tables (Test data included). I've used MSSQL Server 2012.

(3 KB)



After download, open this file using SQL Server Management Studio & execute it. Then you have test data to see how cursor works ! Following is a code snippet of a typical sql cursor example.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

USE TestDB_CURSOR CREATE TABLE #TEMP_TABLE(CUSTOMER VARCHAR(50), ITEM VARCHAR(50)) DECLARE @Cusname varchar(50) DECLARE @Item varchar(50) DECLARE TEST_CURSOR CURSOR FOR SELECT CUS.CusName, ITEM.ItemName FROM tblOrder AS ORD INNER JOIN tblCustomer AS CUS ON ORD.CustomerID = CUS.CustomerID INNER JOIN tblItem AS ITEM ON ORD.ItemID = ITEM.ItemID OPEN TEST_CURSOR FETCH NEXT FROM TEST_CURSOR INTO @Cusname, @Item WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO #TEMP_TABLE VALUES(@Cusname, @Item) FETCH NEXT FROM TEST_CURSOR INTO @Cusname, @Item END CLOSE TEST_CURSOR DEALLOCATE TEST_CURSOR SELECT * FROM #TEMP_TABLE DROP TABLE #TEMP_TABLE

Line no. 5,6 => Declaration of cursor variables.

Line no. 8 => Declaration of cursor [Test_Cursor is the name of the cursor]. There are more cursor options like FORWARD_ONLY, FAST_FORWARD etc... But it's not discuss in here. You can view more details form MSDN

Next, a select statement to retrieve values. Here we are selecting two values. This is the result set we are going to loop.

Then start the cursor
In line no 15 => assign values which are got from select statement into the cursor variables.
Important : Selected columns types and relevant variables types must match each other.

Next thing is While loop. @@FETCH_STATUS = 0 means fetching was successful. If something goes wrong and fetching did not happened correctly it will get a value other than 0 (Usually this will be -1 or -2). If @@FETCH_STATUS = 0 iteration will continue and will stop when it become -1 or -2.

Line no. 18 => Values assigned to cursor variables insert into temporary table created above. This is optional & should change according to your scenario.

Then move onto next raw of the result set and assign cursor variables with relevant values. This iterative process will continue until end of the result set row count.

When everything happened correctly we need to close the cursor & release the resources use by cursor itself. That's it for cursor & you may execute the sql query to see the final output.

Performance Concerns

In generally it is considered as, looping in T-SQL is much slower than the looping structures of other programming languages'  such as .Net, VB6 or Java etc.. But it may vary depend on the scenario that someone is working. It's totally up to you to decide which one to use :)

This post regarding sql cursors is the very first technical post written by me :) Hope you liked it. Got something to say?? Please, do comment below...!
See you soon with a another post.!!!

Read more