Text version of the video
http://csharp-video-tutorials.blogspo...

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.
   / @aarvikitchen5572  

Slides
http://csharp-video-tutorials.blogspo...

All SQL Server Text Articles
http://csharp-video-tutorials.blogspo...

All SQL Server Slides
http://csharp-video-tutorials.blogspo...

All SQL Server Tutorial Videos
   • SQL Server tutorial for beginners  

All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenka...

All Dot Net and SQL Server Tutorials in Arabic
   / kudvenkatarabic  

In this video 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

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