SQL Server Programming Part 3 - Variables

welcome to this wise aisle tutorial in this session we're going to teach you all about using variables in Microsoft sequel server what we're covering in this session is first of all the basics of using variables like we'll start with an explanation of what variables actually are then show you the three main things you'll need to be able to do when working with variables as declaring them assigning a value to them and then referring back to that value later on usually in a query once we've covered the basics we'll move on and show you some more specific uses for variables in sequel server how you can store the results of a query in a variable and then how you can display the values that you've stored in variables using either print or select statements we'll show you how you can read a single record into a set of variables and also how you can accumulate values in variables as well and then finally to finish we'll introduce you to the idea of global variables and some of the useful pieces of system information that you can retrieve so let's get started a variable is simply a space in memory where you can hold a single piece of information while your procedure runs they're useful whenever you want to give your procedures in memory if you'd like to be able to refer to the same value from one statement to the next within a procedure a variable is usually the way to go so here's a small example which demonstrates how variables work what we have a three separate select statements joined together in a Union query each select statement selects three different bits of information from separate tables and each query each selects Thaman uses a where clause and testing for a specific date and the same data is using all three select statements and the query works happily if I execute it I'll see a set of results I get a combination of directors and actors and films from separate tables but what would happen if I wanted to change the date that I'm using at this point I'd have to change it in three separate places so what we're going to do is we're going to use a variable which will store the data that we want to use and then refer to that variable in each of our where clauses the first step in using a variable is to declare it and I'm going to do that at the top of my procedure after my use and go statements I'm going to give myself a couple of blank lines and then begin a single line with word declare if you're familiar with other programming languages such as Visual Basic you may be familiar with using the word dim to declare your variables it's exactly the same idea in sequel server the next thing we must do is think of a sensible name for your variable and bear in mind that all variable names in sequel server begin with them at symbol I'll think of a sensible name is kind of a boring name of there we go at my date then that's going to be the name of the variable that I'm going to use and then finally I can assign a datatype to the variable now I know that the kind of data I want to store in this variable is date and time information so I'm going to write after the word name of the variable the datatype date time just a quick note for people who are more familiar with their with visual basic or visual basic for applications you can also optionally add feet as keyword when you declare your variables it is genuinely completely optional in sequel server my recommendation would be to pick one method either do or don't use it pick that method very early on and then stick to it once you've declared a variable the next step is to store a value inside it so to make that work we're going to add a couple of blank lines again and to set the value of a variable you begin a line with what set you can then type in the name of the variable you want to set the value of the sine of value to and follow up with an equal sign and then the value you would like to store in in sum we use the same gate that I've previously used so now use MD - 0 and R 0 1 so that's setting the value of variable it's very very straightforward that hopefully it's fairly obvious that the data type you pass into the variable must be the same as the data type you use when you declared it but other than that it's very straightforward the final step in this example is to use our variable to replace the individual base that we've used in each of the where clauses and that hopefully again is as straightforward as you'd expect I'm going to copy them in my variable here rather than type it in several times and instead of using the add this the explicit gauge 1970 I'm going to replace that with my variables name I do that three times I can then simply run this query or this procedure in exactly the same way as I did before now gets at this point exactly the same set of results everything from 1970 the beauty of this example though now is that if I simply modify a single value in my variable assignment statement when I execute the procedure again I'll find a completely different set of results hopefully that demonstrates how useful variables can be to speed up the way you write write certain procedures so far we've seen that we can assign an explicit value to a variable using the set statement like this one what I'd like to do next is show you that you also set the value of a variable using the result of a select statement so imagine that as well as wanting to see the list of results in the results panel I'd also like to see a breakdown of how many films actors and directors there are within my set of results in order to make that work I'm going to declare and let's start with just declaring a single variable which allows me to count the number of films so I'm going to Clare available optimum films and the data type of this one I'm going to use the the int data type now instead of using a set statement like the one that I use for my date I'm not going to set the add the variable to be an explicit value what I'm going to do instead is set at enum films equal to the result of a select statement if you want to do this I need to make sure I enclose the selects them into in a set of parentheses or round brackets then all I need to do is write out the Select statement that would generate the count of the number of films from the film table where the film release date is greater than or equal to the my date variable so it's almost exactly the same select same as I used in the first place to generate the list of results but instead of actually selecting the the column names I'm searching account of all the records from that table I've just quickly declared two new variables num actors and num directors and I've set their values using the same technique as we use for the number of films what I need to do now is work out where I can display those values once my procedure is finished running I've got a couple of different choices one thing that I could do if I give myself a couple of blank lines just below my set statements I can simply select the results of my variables into the the results panel again so let's show you quickly how you can do that you can simply say select actually want to display a bit of text as well so I'm going to display a number of films , at num films if I execute this entire page now I'm going to end up with two separate result sets so I execute there we go I can see that I get the original set of results showing me all the film's actors and directors and then my extra result set show me that number to continue in that vein all I need to do is add a union keyword and then say select as a number of excuse me contact number there we go number of actors and then another comma and then the at num actors variable and if I execute the query again I'll add to my little set of results so that's one place that you can share the information from a variable you can select it into the results panel there is one other place that you can display the results of your variables as well and that's on the messages tab using a print statement so what I'm going to do is I'm going to take away I'm going to read that replace the words select with the word print here instead and then just for the moment I'm going to take away the bit of text number of films and only the Union keyword and again I'm going to replace all of this with the word print so what's going to happen in this case when I execute my query is that I don't see an actual result set in the results panel but if I head on to the messages tab I see the values of my variables printed now it'd be nicer if I could display that in sort of a full sentence so number of films equals 239 I have to be quite careful about data types and ID this if I try to tag on that piece of text at the beginning number of films so back button number of films equals and then I try to concatenate in the the number of films variable with a plus symbol when I try to execute the query I'm going to end over there with a conversion error whenever you try to build a sentence whenever you try to concatenate values in the sequel server you must ensure that all the values as part of the sentence are bits of text and I'm trying to commit to add a number to rid of text what a sequel server does in that case is it tries to perform an arithmetic addition they tries to add the word number of films to the value 239 so it's pretty confusing what we're going to do here is simply apply a cast or convert function to convert the number of films variable into some kind of text I'm going to go with VAR char max for this particular short example so if I execute the query this time I ought to end up on the messages tab a slightly more sensible message so I've just quickly added in an extra couple of print statements and concatenated they're sensible message so that we can see the full set of outputs on the messages tab I want to make a quick mention however of this extra output at the bottom the row count which always appears when you keep statements in sequel server on the messages tab this row count is actually associated with a Union query that we run after we've printed out or the results of all our variables so this entire universe entire effectively single Select statement has affected 259 records now sometimes if you're presenting messages on the messages tab so customize messages you don't want the row count message to appear so it's actually possible to suppress that with a simple statement if I add this to the top of my procedure I can simply write set no count on I'm going to execute this procedure again now I'll find that the row count message has disappeared so we've seen now that you can assign values to variables in one of two ways either by assigning an explicit value in the set statement like this or by setting it to the result of a select statement using an aggregate function count min max sum etc what I'd like to do now is move on and show you that you can set the value of a variable in the Select list of a select statement so here we've got a basic query which selects a single record a single actor from that table where the date of birth is after a specific date we've ordered the results in a specific order and what I'm going to do is I want to read in the results of these three fields into three separate variables so to make this work I need to declare three separate variables at the top of my my procedure declare at I'm going to call them simply ID which will be an integer and then I declare at name which will be varchar' moon over Max and then I can declare at date which will again be date and time data so the next job is to assign values to each of these variables and I'm going to do that as I set in the Select list itself so I'm effectively selecting a record into variables to make that work is fairly straightforward all I need to do is say the name of the variable is equal to that's field and likewise I can say then as name equals at a name and subsequently at date equals out a date of birth if I execute the query now one thing you'll notice is that I don't see this set of results I don't actually see a results panel all I get is a message saying that my command has been completed successfully so when you do this you actually don't select the set of records into the results panel all the data is simply being used to be to assign the values of these variables so again if I actually want to make use of the values of those variables I have to display them somewhere so I could write another select statement here which will select the name variable and the dates variable so shoot me out his name and date of birth and then I've actually sneakily already written another query which makes use of the third variable so below here I have another select statement we should like same over film and a character name from a combination of tables where the cast actor ID is equal to the value of my ID variable so I'll get two separate sets of results now when I run this query or execute this this procedure if I execute the whole thing I'll end up with two separate result sets one showing me the actor's name and date of birth and then finally a list of all of the films and all the roles that that character played following on from selecting a record in two separate variables it's also possible to accumulate values in a variable within a select statement so what I have here is a simple variable called name list which shall has been store varchar' data and I'm writing a single simple select statement which likes the actors name from the table in a particular year what we're going to do is we're going to accumulate a list of the actors names in this variable so we'll create a comma-separated list to begin with may this work I need to make sure that I've initialized my variable so I'm going to set that name list equal to an empty string to begin with this technique relies on the fact that the variable has some sort of value already that is not now all I need to do is modify my select list so that instead of just selecting the list of actors names I'm actually setting the value of my name list variable as well so I'm going to say at name list is equal to and then what I'm going to do is I want to make sure that I add my value on each other's name is added onto the end of the current list of names it's going to make nameless equal to what's already in the nameless variable plus the actor's name plus a comma and a space at the end all I need to do now is right at the very bottom of my procedure is to show that information somewhere I'm going to print that name list so I'll come out on the messages tab let's get that properly written in there a name list and if I execute this query now I'll see on a messages tab a list of all the actors who were born in the year that I've asked for one other useful thing you can do here when particularly on you're working with text is rather than separating each value with a comma or some other character you can actually insert a line breaks or line feeds you can do that using the char function and there are two characters you can use here chart n which is the carriage return I believe and there's also chart 13 which is the the line feed character if I execute this query now I'm going to end up button all my values on a single line I'm going to end up with blank lines between each one I sped that I take away one of these characters if I just include chart n that will actually get me to line each of each value on a separate single line so it's not the most useful thing to learn in the world is the most common thing you'll do but it's useful to know how you can do that Rajini T so we've talked about how you can declare your own variables assign values to them and then retrieve those values what I'd like to do to finish off this video is just show you a few of the built-in global variables or system variables there may be some useful bits of information you can find here I'm going to use the Select statement rather than the print statement here just because it helps me with the intellisense list if you try to type in a couple of apt symbols when you're selecting you'll find you get a long list of what I've referred to as global variables these are built-in system bits of information so useful bits of information you can find here for example the server name so if I execute this simple statement here it shows me the name of the server that I'm currently working on and there are several other bits of useful information you can find with the global variables as well so for example as well as displaying the server name I can also select the version of sequel server that I'm working on if I execute this I need a slightly wider column to display all the information there we go they're all very useful stuff I'm sure what other things can we do and there's a global variable called @ @ row count which if you remember earlier on we suppress the other row count when we wrote our a slightly longer procedure I did it turned off the count of number of Records affected and if I did something very quick and simple like select everything from the actor table for example my my row count selecting the row count will there will actually show me the number of Records affected by the last statement it should be exactly the same number as is displayed here on the messages tab in fact but if you suppress the row count all you wanted to pick up on the number of Records you've selected or deleted or or modified then the row count variable can be quite useful as well it can be worthwhile investigating a few of these and view these global variables to see what sort of information you can retrieve I thought it was a nice way to finish off our video on variables if you've enjoyed this training video you can find many more online training resources at ww-why Zeljko UK

Channel: WiseOwlTutorials

Views: 143,830

Keywords: sql, sql server, variables, programming, procedures, wise owl

Id: NmYaOlcbfZM

Length: 19min 8sec (1148 seconds)

Published: Wed Nov 21 2012