Dynamic SQL in SQL Server
HTML-код
- Опубликовано: 26 мар 2017
- Text version of the video
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 RUclips channel. Hope you can help.
/ @aarvikitchen5572
Slides
csharp-video-tutorials.blogspo...
All SQL Server Text Articles
csharp-video-tutorials.blogspo...
All SQL Server Slides
csharp-video-tutorials.blogspo...
All SQL Server Tutorial Videos
• SQL Server tutorial fo...
All Dot Net and SQL Server Tutorials in English
ruclips.net/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 Наука
Great! Can't wait to see the rest of the episodes in this series. Thanks!
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
you are a great man thank you very much....
Very good very helpfull vedio. Thanks alot sir for your contribution and giving precious time to educate people like us....
Venkat you are excellent!
wow wonderful thanks a lot ,i am very grateful to you sir
Thank you so much 🙏🌞watching now in 2022 💃
Thank you so much! very good!
Thanks sir ! for grate information ,Because of you the future of many people has become 🙏🙏
Thank you very much Venkat, if possible could you please upload videos on WPF and Xamarin mobile app development using Visual studio
Great Video.. Thanks for the clear concept explanation.
thank you from Yemen
Thank you very much
Great video. expecting design pattern videos!!!
great video. thank you venkat. could you go over migration of a sql database?
Awesome! very helpful
Thanks for explaining but what is the use of dynamic query? I can do this with a normal proc too.
Thanks a lot
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.
sir can we use two begin form in a mvc view page
Great Video
Hope you can do some videos on how to Create a Balance Sheet and Income Statement in SQL. Thanks!
Very helpful and easy to understand
This is cool.How can we replace the SPs in my project with this dynamic sql?
venkat sir please upload big stored procedure which includes select ,insert ,update ,delete in one procedure for real time project
Very good
But how dose it reduces complexity?
what are precise advantages of Dynamic SQL
please make videos for .net core
I want to store value returned from a dynamic query into a variable. Please let me know how it is possible
Can u do a video on how to update multiple specified columns from one table to anothe table by using dynamic sql
If possible can you make 2 or 3 sessions on GIT source control?
manoj tharak zzz zzz xzzs xzzs zs
When I watched this in full screen I mistakenly clicked on the screen hoping I could copy some line or scroll up but then I realized I was just watching a video
Hahahahahhahahahahhahahahahahahhahahahahahahahhahshahahahhahahahahahhahahahahahahhahahahahahahahahahahahahahhahahahahhahahahahahahhahahahahahahahahahhahahahahahahahahahhahahahahahwhhahahahahhahahahahahahahhahahahahahhahahahahhahhahwhaha
Srilatha vaka(25556) in this example we can find inner side value
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
after that line we are declaring that parameter like @Params = '@id int'
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'
set @params = '@id int'
Execute sp_executesql @sql,@params,@id=1
-------------------------------------------------
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
In your scenario it is same flow but Dynamic SQL is used for generating and executing queries at runtime mostly in web applications where you have to fetch data from some form or some other place and then based on that query should be executed.
Thank you very much
Nice video sir..