Dynamic SQL in SQL Server

we will discuss

1. What is Dynamic SQL
2. Simple example of using Dynamic SQL

What is Dynamic SQL
Dynamic SQL is a SQL built from strings at runtime.

Simple example of using Dynamic SQL : Let's say we want to implement "Employee Search" web page as shown below. 




Depending on the serach fields the end user provides, we want to search the following Employees table.


Here is the SQL Script to create Employees table and populate it with data

Create table Employees

(

     ID int primary key identity,

     FirstName nvarchar(50),

     LastName nvarchar(50),

     Gender nvarchar(50),

     Salary int

)

Go

Insert into Employees values ('Mark', 'Hastings', 'Male', 60000)

Insert into Employees values ('Steve', 'Pound', 'Male', 45000)

Insert into Employees values ('Ben', 'Hoskins', 'Male', 70000)

Insert into Employees values ('Philip', 'Hastings', 'Male', 45000)

Insert into Employees values ('Mary', 'Lambeth', 'Female', 30000)

Insert into Employees values ('Valarie', 'Vikings', 'Female', 35000)

Insert into Employees values ('John', 'Stanmore', 'Male', 80000)

Go

One way to achieve this is by implementing a stored procedure as shown below that this page would call.

Create Procedure spSearchEmployees

@FirstName nvarchar(100),

@LastName nvarchar(100),

@Gender nvarchar(50),

@Salary int

As

Begin

     Select * from Employees where

     (FirstName = @FirstName OR @FirstName IS NULL) AND

     (LastName  = @LastName  OR @LastName  IS NULL) AND

     (Gender      = @Gender    OR @Gender    IS NULL) AND

     (Salary      = @Salary    OR @Salary    IS NULL)

End

Go

The stored procedure in this case is not very complicated as we have only 4 search filters. What if there are 20 or more such filters. This stored procedure can get complex. To make things worse what if we want to specify conditions like AND, OR etc between these search filters. The stored procedure can get extremely large, complicated and difficult to maintain. One way to reduce the complexity is by using dynamic SQL as show below. Depending on for which search filters the user has provided the values on the "Search Page", we build the WHERE clause dynamically at runtime, which can reduce complexity.

However, you might hear arguments that dynamic sql is bad both in-terms of security and performance. This is true if the dynamic sql is not properly implemented. From a security standpoint, it may open doors for SQL injection attack and from a performance standpoint, the cached query plans may not be reused. If properly implemented, we will not have these problems with dynamic sql. In our upcoming videos, we will discuss good and bad dynamic sql implementations.

For now let's implement a simple example that makes use of dynamic sql. In the example below we are assuming the user has supplied values only for FirstName and LastName search fields. To execute the dynamicl sql we are using system stored procedure sp_executesql. 

sp_executesql takes two pre-defined parameters and any number of user-defined parameters.

@statement - The is the first parameter which is mandatory, and contains the SQL statements to execute

@params - This is the second parameter and is optional. This is used to declare parameters specified in @statement

The rest of the parameters are the parameters that you declared in @params, and you pass them as you pass parameters to a stored procedure

Declare @sql nvarchar(1000)

Declare @params nvarchar(1000)

Set @sql = 'Select * from Employees where FirstName=@FirstName and LastName=@LastName'

Set @params = '@FirstName nvarchar(100), @LastName nvarchar(100)'

Execute sp_executesql @sql, @params, @FirstName='Ben',@LastName='Hoskins'

This is just the introduction to dynamic SQL. If a few things are unclear at the moment, don't worry. In our upcoming videos we will discuss the following

1. Implementing a real world "Search Web Page" with and without dynamic SQL
2. Performance and Security implications of dynamic sql. Along the way we will also discuss good and bad dynamic sql implementations.
3. Different options available for executing dynamic sql and their implications
4. Using dynamic sql in stored procedures and it's implications

Once we discuss all the above, you will understand 
1. The flexibility dynamic sql provides
2. Advantages and disadvantages of dynamic sql
3. When and when not to use dynamic sql