SQL Server Programming Part 13 - Dynamic SQL

welcome to this YSL video in this tutorial we're going to show you how you can use dynamic SQL in Microsoft sequel server we'll start the video with a quick explanation of what dynamic SQL actually is and then we'll show you two techniques you can use for executing dynamic SQL statements we'll spend a bit of time teaching you how you can build the dynamic ask your string using concatenation and that's going to include a little hint about how you deal with problems arising from concatenating numbers as well once we've covered the basic techniques we'll move on to show you a few practical uses for dynamic SQL mostly that's going to involve creating stored procedures so we'll teach you about using parameters in a stored procedure so your user can pass in bits of a dynamic SQL statement we'll show you how you can use the in operator to create some quite nice dynamic queries and finally we'll show you one of the main dangers of using dynamic SQL and that's called SQL injection so let's get started using dynamic SQL means that you can take any valid SQL statement convert it into a string of text and then execute that string of text as though it was the original SQL statement which admittedly sounds fairly pointless when you put it like that but there are good reasons for wanting to use dynamic SQL and we're going to mention a few of those throughout this video first of all I'd like to show you the two different techniques you can use to execute a dynamic SQL statement the simplest way to use dynamic SQL is to use the execute command so in order to make that work I first of all need to convert my SQL statement into a string and I can do that by simply adding a single quote to the front and to the end of it to turn it into a string of text then at the start of that string of tacks I need to write the word either exact for short or execute if you prefer the full keyword and then I need to enclose my string in a set of parentheses or round brackets and that's it I'm ready to go so when I execute this code now what I'm going to get at the same results as though I had just executed the Select statement by itself apparently not a very useful example but as I say we will show you uses for this further through the video the second way to use dynamic SQL involves using a system stored procedure we have to start again by converting our Select statement into a string of text so you can do that by adding the single quotes to either side of it and we also have to make sure that the string of text is a Unicode string so as they type has to be n varchar' for this store procedure to work the easiest way to achieve that is just add the letter n to the start of the character string and that creates a Unicode string then we can go about calling our stored procedure so we can do that by writing the word exact or execute again and then the name of the stored procedure which is called SP underscore execute SQL so it's a built-in system store procedure which accepts a unicode string which can be interpreted as a valid SQL statement so what I need to do again is to execute this code and I'll get exactly the same results as I saw earlier as of our a couple of different ways of using dynamic SQL it's worthwhile quickly mentioning what the key differences between the techniques are so of course apart from the obvious ones such as the first technique being shorter to type as you haven't got to call the stoppers easiest name and it also being a little bit less fussy about datatypes as well you don't have to ensure that it's a Unicode string when you use the executi word the key difference comes from the query plan that is generated when you use the stored procedure so whatever SQL statement you pass to the stored procedure SQL will generate a query plan for that select statement and store it in a cache and that means that subsequent times that you run this same select statement or even moderately different ones ones where you perhaps tweak the Select list those will run more quickly than the first time you do so and that's something that using the execute keyword by itself doesn't do every time you execute this select statement using the execute keyword is as though it was running for the very first time so using the data via their stored procedure can make your queries your dynamic SQL more efficient there is another advantage to using the stored procedure as well and that's going to become clear when we start talking about using parameters so there are extra arguments for the stored procedure which allow you to add parameters to a select statement and which can be very very useful to us later on the real powering dynamic SQL comes from when you start concatenate in your statements from various individual bits of text so what we're going to do in this example is create a simple statement which allows us to replace the name of the table that we're selecting our records from it's this small part of our statement is gonna become the dynamic part of our dynamic SQL now this technique works best if you're using variables so the starting point here is to declare a couple of simple variables one that can hold the name of the table I'd like to use I'm going to declare that table name and the data Sabbath Academy and varchar' and I know my table names have a maximum length the boundary in twenty-eight characters so I'm gonna fix the the maximum limits of that data size 128 and it also makes sense I think as well and to declare another variable which holds your main SQL strings I'm going to declare a variable called SQL string and that's gonna be ended our char and then these max the next step is to set the value of my table name variable and I can do that with a simple set statement so let's say set a table name and I'm gonna make it equal to a CTP I'll film Lisa the same table name that we're already using in our select statement now that I've got that value stored in my table name variable I want to join that together with an the main SQL string to generate a complete statement so let's modify this line that we have here I'm gonna set at SQL string and made them equal to the starting part of my select statement is always going to be the same it was gonna be selecting star from but it's this part that's going to become dynamic so I'm gonna remove the TBL film table name remove that from the statement and instead I'm going to concatenate by using the plus symbol whatever table name is stored in my table name variable all that we need to do now is execute the resulting SQL string and to do that I'm going to use the system's stored procedure so I'm going to choose to exec SP underscore execute SQL and I'm simply going to call on my SQL string stored in my SQL string variable so there we go if I execute this query now what I should see is again an entire list of films because that's the name of the that I pass into the statement plucking happily now easily modify the name of the table that I'm using so if I choose actor that will automatically modify the SQL string that's executed and maternal list of actors instead I can continue doing this to change the name of the table to anything that I like one thing you have to be careful of when you're concatenated strings in SQL it's when you try to add a number to the string so in this example I'm trying to create a system whereby I can choose the number of Records I display from a specific table in a specific order so I have a variable here called at number which can hold an integer and the idea should be that I can set a number equal to whatever number I want so I only have a 10 again what I've been like to do is build an SQL string which incorporates that value so the value stored in the variable so I'll separate out the parts of my SQL string here that I know will be fixed so that's the Select top and then no more we fell in there and then star from TBL film etc and then I can simply replace that gap the absolute value 10 with a reference to my variable called at number so you can concatenate in the number variable finally I want to store the entire result of this concatenation in my SQL string variable so I can say set at SQL string equals that statement the problem is when I try to execute this query now I'm going to see an error message appears a conversion error and it's alternators tried to convert the N bar chart value select top into the data type int so what SQL is actually try to do is convert the phrase select top into an integer so they can arithmetic alee add the words select top to the number 10 which is clear that complete nonsense what we need to do is the opposite we need to convert the number into a string and there are two techniques for doing that you can either use the cast function or the convert function so I'm going to create another variable which will hold the converted version of my number so I'm going to declare as a number string which will be a voucher or enverga and I'm gonna give it a maximum limits of four characters and then after I've set the number variable what I can do is set the value of my number string variable by conversing the first one so I can use either cast or converse and oneis cast because it's shorter it's type more than anything I'm gonna cast a number as n varchar' four and there we go all I need to do now is replace my reference of a a written on them a variable in my SQL string with a reference to number string instead so if I execute this statement now what I should find it is it finally works and I might change the number of records that I want to retrieve that should also affect the results as well so there we go there's using numbers in an SQL string making sure you can handle the conversion error that you're likely to receive now it's fairly unlikely to see or use simple scripts like the ones we've created so far to use your dynamic SQL statements more commonly you create a stored procedure into which you can pass a parameter which would affect the Select statement itself so what we're gonna do here is create our own stored procedure which allows us to pass in the name of a table it's going to create proc SP variable table it's about table that would be better and this put this still procedure is going to have a single parameter so in a set of round brackets after this property just name we're going to have a single parameter called at the table name as we saw earlier on and that datatype is going to be n bar char 128 again I'm then going to say this store procedure the definition of this table procedure as I'm gonna store the logic within a beginning and an end block we've created videos on how you create stored procedures previously so that goes into this in a lot more detail about what's a lot those ones up but this basically saw procedure is called SP variable name sorry variable table and and a table name which must be a string and the end result what I want to happen is that my my procedure shows me the results of the the dynamic SQL statement that will be built so let's continue and declare a variable which can hold our SQL string so declare our SQL string which will be n bar char max and then I can simply build my SQL string by saying set at SQL string equals M and select star from plus whatever value has been passed him through my table name parameter so it's exactly the same technique as River doing previously but using the stored procedure makes that the code much more reusable the final step then is to execute my system store procedure SP underscore execute SQL and pass in my full at SQL string and there we go so if I execute this code now that will generate my store procedure called SP valuable table and I get a lovely message saying that is completed successfully to use that store procedure now I can copy its name into a new query window so I knew that with ctrl + n on the keyboard and if I say exec followed by my store procedures name I can then ask it for say TBL film the first time and if I execute this it will show me a list of films if I modify the name of the table and execute it again it gives me a different list of records so that's the beauty of using dynamic SQL with a stored procedure it encapsulates the logic as all store procedures do I suppose encapsulate the logic of a complex procedure and allows you to call it and reuse it in a much more simple fashion if you're going to use store procedures to generate your dynamic SQL it's worthwhile knowing a couple of extra things about store procedures in general so the fact that you can modify or alter a store procedure after you've created it so for this example if I decide that I'd like to include an extra parameter for instance I can say alter proc SP variable table and I'm going to add in another variable or sorry another parameter called at Number you hopefully see where I'm going with this and that's going to be int it's when I call my stop procedure now I can pass in the number of records I'd like to return Y then got to do is convert that number into some kind of text data so probably n varchar' so I'm going to do that in the same way I did earlier on I'm going to declare at number string which will be n VAR char I'm gonna give it a max of 4 and then I can set at number string equal to cast number as n bar char for now I have to build in and that value into my main SQL statement so in my line which sets my SQL string I don't just want to select everything I want to set the top so many records from my table so I'm going to add the top keyword and then I'm gonna isolate the individual bits of my SQL string so I'm gonna select top and then it's at this point that I'd like to build in or draw on my number string variable so I want to concatenate plus a number string plus I've gotten the string part oh yeah I'm a string and plus everything from plus table name so when all this is joined together it generates a valid SQL string trust me on that one so all I need to do now is is execute this code to make the alterations to my stored procedure so if I execute that and turn to my test code from earlier on I've now got the ability to add on a second parameter it's going to pass in a number 10 and I should now return just 10 records from the table I've asked for I changed the table name and change the number I've asked for it will give me a different number from a different table so there we go another neat demonstration of how to use store procedures to encapsulate your dynamic SQL statements dynamic SQL is also quite handy for making the contents of an in operator dynamic and flexible so in this example I'm trying to display films that were released in a certain list of years it's my in operator allows me to specify a comma separated list of values that I'm looking for if I execute the query I'll see a list of all the films released in 1980 1990 2000 etc so that's affected by whatever list of entries I enter here so to make this into a dynamic SQL query I'm going to create another store procedure so I'm gonna say create proc and all this SP film years and I'm gonna simply pass in or allow my user to pass in via a single parameter at year list and that's gonna be NPR max so rather than passing in a list of numbers my users gonna pass in a string of text that contains that comma separated list of numbers so now I need to add in the various other parts of my store procedure so the ASCII word I'm gonna have a beginning and end block so right at the bottom I'll have end and then just to make it a little bit clearer to read and indent the slice named itself one space so you can see what's part of my SQL statement my store procedure standard things I'm going to do then just as we've done previously I'm going to declare a variable called at SQL string and that's going to have be n bar char max and that's going to allow me to hold the full SQL string then I'm going to set the real value of that variable by building it up out of the component parts of this select statement so everything from the words select up to the open round brackets there the open parenthesis is part of the SQL string will always be the same this part will be different the list that I'm going to pass in but also the following parts that close round brackets and all by film release date will also always be the same so all I need to do now is replace this comma separated list of values here by concatenated my at year list parameter there's a neat feature of SQL when you're writing it is you can actually split a string of text across multiple lines I know make it a little bit more sense a little bit easier to read I think there's one single line present neat trick turtle lay things out a bit more neatly hopefully what I can do now then is I forgots about the the end smooch or these unicode strings what I should be able to do now but I remembered to do that is to call on my store procedure our system store procedure called exec SP underscore execute SQL and if I pass in my standard @ SQL string parameter variable I should now be able to run and create my store procedure so all that remains is to give it a quick test to see if it works if I copy the name of my store procedure and then create a new query window by pressing ctrl + n I should be able to say exec pasting the name of my store procedure and then as a string I should be able to simply say things like let's give it 2000 2001 2002 for example when I execute this my list of results should reflect the years that I've passed in its my store procedure while we're talking about stored procedures and parameters I thought it was quickly worthwhile mentioning that the SP execute SQL store procedure actually has its own list of parameters as well so so far we've only been in providing a value for the first parameter and that's the SQL string that we want to execute but it's possible to actually parameterize that SQL string itself so what I mean by that is you can do things like this you could add a where clause to the to the Select statement your friend execute and simply write in things like I'm gonna ask for where filmin time minutes is greater than the value of parameter so I'm gonna say I'm gonna call my parameter at length now of course that predominant actually exists so the second parameter of our stored procedure allows us to define any extra parameters and you do that again as a string of text so it's an N bar chart string where at the length int so that's the definition of any parameters that I've referred to in my Select statement the final parameter of SPX QJ SQL is to set the value of any parameters you've defined and you do that not as a string you do it as a as a literal sort of definition as assignments of a of a parameter so at length equals let's say 120 so it's a bit of a strange way of doing things you refer to a parameter that doesn't yet exist in your statement you then define that parameter and subsequently set its value in the final argument of the final parameter if I execute my script what I should see is a list of results why the run time in minutes is greater than 120 if I modify that number 180 that I'll should return a much shorter list results and there we go you can extend this and generate even more parameters for your f your Select statement so if I add in an extra criterion into my where clause I can say and film Lily State is greater than a variable of parameter called at start dates for instance if I want to define that parameter now I get to the second parameter of SPS execute SQL and add another definition to my place so in the same string separated with commas I can define that start date star date as date time the way that you then assign a value to that parameter that you've created is via an extra arguments or an actual parameter of SP excuse SQL so you don't set the value of start date in the same parameter as you do at length you actually add another parameter and say at start date which you just have to pretend exists star date equals and then pass in the dates or value you want to assign to that parameter so I'm going to look for any films whose run time is longer than a hundred eighty and was released after the first day of 2000 is acute all of that that's exactly the set of results I'll see now you can't really talk about dynamic SQL without mentioning one of its biggest potential dangers and that something referred to as SQL injection so I'm gonna go back to a previous stored procedure we created one that allows me to pass in the name of a table and for it to return a set of results from that table so just to remind you what the store procedure looks like it's a single parameter table name we passed that in when we call the procedure that's built into the SQL string disliked and everything from that table and then the string is executed so when I call this as intended it allows me to see a list of films about you asked for the table of films it'll ask me to see a list of actors if I ask for table actors and any other table that I asked for now imagine that this stored procedure isn't sitting in a simple scripts like this is actually sitting behind the search function of a website for instance so for instance I the one on the wise our website there's a little search box there and it might well be that whether you bother you type into that search box when you click go that could be executing a store procedure which returned results from the server now imagine somebody was using that search box who knew a little bit about how SQL works somebody might decide to do something horrible like this they could add a semicolon to the end of the parameter they've passed them the semicolon is the statement terminator in SQL you don't need it in Microsoft transact-sql but it doesn't harm it if you do add a semicolon so each statement is ended by a semicolon so if they've ended one statement they could then choose to begin another and they might choose to do something horrible like drop table for instance and if they knew or predict a ranger guessed the name of a table in your database for instance I know that I've got a table in my database as I've just created it called TBL test and there it is so if I said to drop table TBL test when I execute this script now I'm actually passing a string which consists of two complete SQL statements to the SP execute SQL store procedure so execute it although I'll see my list of results as I'd expect what's also happened if i refresh my table of my folder of tables you'll see that TBL tests will disappear that's a horribly frightening idea imagine a website which contains those sorts of search boxes with dynamic SQL in the background into which you can pass any sort of SQL string you like our Y cell web logs is surprising how frequent people attempt to perform SQL injection attacks on a daily basis we can see people searching for silly things like SQL semicolon and then something horrible and NASA they try to do afterwards fortunately the wise our websites got some controls in place that prevent that from happening otherwise we wouldn't be running a very successful business but there you go that that's one of the dangers you have to aware of now there are many many different ways of them of controlling for for SQL injection attacks rather than going to them in this video what I'm going to do is I'm going to point you to the the ultimate resource for the dynamic SQL it's a website called them called summers cog which I will I will post a link to this as well in the in the description of the video but this is by far in a way I think the best resource on the web for dynamic SQL and there is a nice little section here on dealing with SQL injection attacks and what that actually is and how you get around it so I'll leave that up to you to read about because there are several different techniques for for solving the problem and I kind of brings us to the end of this video so all I'm going to do is leave you with with a little famous cartoon by x @k CD which is all about a very geeky joke to do with dynamic SQL but hopefully having watched this video you're in a position to understand it if you've enjoyed this training video you can find many more online training resources at www.weiu.net

Channel: WiseOwlTutorials

Views: 84,349

Rating: 4.9332094 out of 5

Keywords: sql, dynamic, injection, wise owl

Id: MiAwOoelu9k

Length: 27min 40sec (1660 seconds)

Published: Fri May 03 2013