Text version of the video
http://csharp-video-tutorials.blogspo...
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
https://www.youtube.com/playlist?list...
All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenka...
All Dot Net and SQL Server Tutorials in Arabic
https://www.youtube.com/c/KudvenkatAr...
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
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
Dynamic SQL in SQL Server asp.net core docker | |
| 329 Likes | 329 Dislikes |
| 55,471 views views | 524K followers |
| Science & Technology | Upload TimePublished on 27 Mar 2017 |
Không có nhận xét nào:
Đăng nhận xét