Dynamic SQL in SQL Server

Text version of the video

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.

Slides

All SQL Server Text Articles

All SQL Server Slides

All SQL Server Tutorial Videos

All Dot Net and SQL Server Tutorials in English

All Dot Net and SQL Server Tutorials in Arabic

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)

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

[krismaly]

I Love this video

All developers to know about sp_executeSQl of system stored proc

It is very useful in front end development

Host explains every functionality crisply and crystal clear.

Host also purposefully includes developer mistakes in writing SQL and explains how to resolve that problem

Thanks for educating the community and appreciate your volunteer-ship.

Thanks a bunch

[smileplease]

Thank you so much 🙏🌞watching now in 2022 💃

[viveksingh-xlut]

Thanks sir ! for grate information, Because of you the future of many people has become 🙏🙏

[dipalijaiswal]

Hi, Your videos are very helpful in understanding the SQL concepts in-depth, but I was hoping if I could download a PDF version of the course for revision. It will be much appreciated.

[pavithravvenu]

Great Video.. Thanks for the clear concept explanation.

[Eeshwargiri]

venkat sir please upload big stored procedure which includes select, insert, update, delete in one procedure for real time project

[MrCardeso]

Great! Can't wait to see the rest of the episodes in this series. Thanks!

[PinasPiliNa]

Hope you can do some videos on how to Create a Balance Sheet and Income Statement in SQL. Thanks!

[ihsanullah]

Very good very helpfull vedio. Thanks alot sir for your contribution and giving precious time to educate people like us....

[rakiyadav]

Can u do a video on how to update multiple specified columns from one table to anothe table by using dynamic sql

[CRVgarage]

Thanks for explaining but what is the use of dynamic query? I can do this with a normal proc too.

[farooqmd]

Thank you very much Venkat, if possible could you please upload videos on WPF and Xamarin mobile app development using Visual studio

[narendarsingh]

wow wonderful thanks a lot, i am very grateful to you sir

[gosmart_always]

I want to store value returned from a dynamic query into a variable. Please let me know how it is possible

[ashtafahmed]

you are a great man thank you very much....

[akshayr]

very use full, but i have some doubts.
Inn dynamic sql
First we write select statement in which we are using parameter like S_id= @id
and then we are passing value to parameter using sp_Executesql keyword

which is not possible in normal sql statement.

IN Dynamic Query
declare @sql nvarchar(1000)
declare @params nvarchar(1000)

set @sql = 'Select * from Student ' + 'where S_id = @id'

In Normal SQL Statement
Select * From Student where S_id = @id
Declare @id int
set id = 1


Could you please tell how the program flow will happen in dynamic SQL query

[pvreddy]

Srilatha vaka(25556) in this example we can find inner side value

[zaminhassnain]

what are precise advantages of Dynamic SQL

[manojtharak]

If possible can you make 2 or 3 sessions on GIT source control?

[peterl]

great video. thank you venkat. could you go over migration of a sql database?