Dynamic SQL in SQL Server

this is part 138 of sequel server tutorial in this video we'll discuss what is dynamic sequel and a simple example of using dynamic sequel so what is dynamic sequel in simple terms a dynamic sequel is a sequel built from strings at runtime let's understand this with an example let's say we want to implement an employee search form that looks like this notice on the form we've got four search fields first name last name gender and salary using this employee search form we want to search this employees table it is the sequel script to create and populate this employee's table let test data I'll have to script available on my blog in case you need it I've already executed the script so to search this employee's table using this employees form we may have a stored procedure that might look like this notice on the employee search form we've got four search fields and the search employee stored procedure has got four input parameters corresponding to those search fields and within the body of the stew procedure we are doing all the rows and columns from employees table their first name column equals whatever value we have passed to the first name parameter or first in parameter is null so when it's first in parameter going to be now if we don't specify anything for the first name field then this first name parameter is going to be not so either the parameter is now or first name column equals whatever value the first name parameter has got so if either of those conditions are true then give me that employee row and we have got other conditions as well for each search field now here we only have full for search fields on the form so this stored procedure is not that complicated now here we are not using any dynamic sequel it's all static sequel in a stored procedure let's quickly test the stored procedure to make sure it works is expected at the moment within our employees table we've got seven now let's say we want to use our SP search employee stored procedure and find all employees whose first name is Marc and whose last name is Hastings we're not going to make use of the other parameters so in this case gender and Saturday parameters are now so when we execute this notice we get an employee whose first name is Marc and last name is Hastings let's say we want to find all female employees so in this case right here except the gender parameter we have set all the other parameters to now gender is set to female so when we execute this we get all female employees now this stored procedure in this case is not very complicated as you only have full search filters but what if there are 20 or more such filters this stored procedure can get complex and to make things worse what if we want to specify conditions like and or etc between these search filters this short procedure can get extremely large complicated and difficult to maintain one way to reduce this complexity is by using dynamic sequel so what we can do here is depending on for which search filters the end user has provided the values on the search page we can build the bearclaw's dynamically at runtime which can reduce complexity however you might hear arguments that dynamic sequel is bad both in terms of security and performance that is true if the dynamic sequel is not properly implemented from a security standpoint it may open those foreseeable injection attack and from a performance standpoint the cached query plans may not be properly be used if properly implemented we will not have these problems with dynamic sequel in our upcoming videos we'll discuss good and bad dynamic sequel implementations for now let's implement a search example using dynamic sequel fire up a new query editor vendor and in here I have declared a variable at 0 of type and we're care remember dynamic sequel is formed from so in this string variable let's do our dynamics eco statement let's assume the end user has not provided values for any of these search fields that means we want to load all the rows and columns from employee's table which also means we don't have to have this where clause at all so depending on the values that the user is provided for these search fields we are going to build our Sifl statement so here our sequel statement is now just going to be select star from employees so in this variable we have our dynamic sequel statement the obvious question that comes to mind at this point is how to execute this dynamic sequel statement to execute the dynamic sequel statement we use systems to procedure SP underscore execute SQL and this distance tool procedure we pass the dynamic sequel statement that we want to execute so let's execute all these three statements together and notice we get all the rows and columns from our employees table now let's assume the end user has provided values for first name and last name fields on the search form that means they have to include the bearclaw's with first name and last name so to a dynamic sequel statement I'm going to append the where clause where first-name equals at first name so here they're using parameters within our query and last name equals at last name now in this dynamic sequel statement we have got parameters so we have to declare these parameter types and to do that I'm going to declare another variable and let's call this at params and in this variable they are going to declare the parameters that we have in our dynamic simple statement so set at params equals so this at first name parameter is of type n work at and let's set the length to 100 similarly last name parameter is also of type n where care and its length is also 100 now this system store procedure has got two predefined parameters the first parameter is the dynamic sequel statement that we want to execute and the second parameter declares the parameters that we have in our dynamic sequel statement so in our dynamic sequel statement as you can see we have got two parameters at first name at last name and using this variable we have declared the parameters so the second parameter for the system stored procedure is this params variable which contains the parameter declarations that we have in our dynamic single statement and then we also have to supply values for these parameters that we have in our dynamic sequel statement so the rest of the parameters that we can have for the system stored procedure are user defined parameters so now we are going to pass values for first name and last name parameters so at first name equals in a let's say for example mark is the first name and let's set last name to Hastings so now let's go ahead and execute this notice we have an error message it says in correct syntax near equals let's understand why we are getting the data and to do that let's first comment this execute statement and let's print our c2 statement that we have here so I'm going to use the print statement and bassett this variable X equal so when we execute this look at the query that we have select star from employees where there is no space between employees and where that's the reason why we are getting that statement and the reason we don't have that space is because either here or here we don't have a space so let's include a space there and now when we execute this notice now we have caught space between employees and where clause so let's comment this print SQL uncomment our execute statement and let's execute this one more time notice now we get mark Hastings as expected so here is that dynamic sequel example that we just looked at to execute dynamic sequel we use system stored procedure SP underscore execute SQL it takes two predefined parameters and any number of user-defined parameters as you can see the first parameter is mandatory and it contains the dynamic sequel statement that we want to execute the second parameter is optional and this parameter is used to declare the parameters that we have in our dynamic sequel statement and then we can have any number of user-defined parameters so within our dynamic sequel statement we've got two parameters at first name and at last name so we are using the rest of the parameters here to pass values for those parameters that we have in our dynamic sequel statement notice here we are using parameter names as well the parameter names are optional so when we use parameter names we can have those parameters in any order for example we can have of a lastname parameter first and then our first name parameter so when we execute this it still works exactly the same way as before but then these parameter names are optional so if I don't use parameter names then it's going to use the parameter position to specify which value is for which parameter so here if I am passing Hastings first then it's going to treat this value is for first name parameter and Mark for last name but that's not what we want we want mark to be the value for the first name parameter so we need to specify that in the first position and then Hastings in the second position so when we execute this it again is going to work exactly the same way as before this is just the introduction to dynamic sequel if a few things are unclear at the moment don't worry in our upcoming videos we will discuss implementing a real world search web page with and without dynamic sequel performance and security implications of dynamic sequel and along the way we'll also discuss good and bad dynamic sequel implementations different options that are available for us for executing dynamic sequel and their implications and finally using dynamic sequel in stored procedures and implications once we discuss all these then you will understand that flexibility dynamic sequel provides advantages and disadvantages of dynamic sequel when and when not to use dynamic sequel so please stay tuned thank you for listening and have a great day

Channel: kudvenkat

Views: 116,061

Keywords: dynamic sql sql server tutorial, dynamic sql example, sp_executesql example in sql server, sp_executesql multiple parameters, sp_executesql system stored procedure, sp_executesql parameters example, dynamic query in sql server with parameters, why we use dynamic sql, Procedure expects parameter '@params' of type 'ntext/nchar/nvarchar', dynamic sql youtube

Id: n-hkNm2Q9fw

Length: 12min 12sec (732 seconds)

Published: Mon Mar 27 2017