Video Transcription

Welcome back everyone. In this video, we’re going to take a look at variables and SQL. So variables are a little bit different than compared to your normal programming languages, primarily just the way they syntax that they’re, that they’re declared their scope and things like that. And we’ll be covering variables briefly in this video. And then we’ll cover them a little bit more in depth as we get to more some more complicated SQL examples. But to declare a variable, you actually use the declare statements. But you do not have to actually initially initialize a variable with a value, you can leave it blank, the default No, the default value is no if you do not include an as an initial value. So the syntax is always though included an at symbol, and the at symbol is going to make this name a variable Without it, your variable declaration will not work.

So declare some variable with the at sign, and int the data type it comes after the variable name. And then if you want to include initial value, you have to you can then do the assignment operator equals zero. And so since the assignment operator is being or the equals operator here is being used, and the declaration of a variable, not in a Boolean expression, it’s not it’s used as the assignment, not a Boolean comparison. So, again, there’s a whole bunch of different data types that we can utilize here, I’m only going to showcase a small number of them in my video here, and in the notes that I will link to documentation that will give you the long list of data types that are available to you to use inside SQL Server. So we can actually declare more than one variable as well, and one single statement. So you can’t do declare, XYZ, declare XYZ. But it’s more common, a little bit cleaner to do it in one single declare statement. So declare, and then each of those variables that you want to declare each one separated out by a comma. And this example here, I’m just using a integer int and a string type in bar car. And remember that the in is forcing that to be a Unicode string, and the in double quotes, and single quotes is an empty Unicode string.

Without the in next to the quotes, that string would just be a normal string. Not as critical here, when we’re talking about hard coded values. But when we talk about user input, it can be tricky to make sure that the input has been filtered out to only include Unicode characters, or UTF, eight or whichever encoding that the strings actually represent. But later in the class, when we start talking about integrating applications to utilize databases, we’ll talk a little bit more about filtering and things like that as far as from the UI to the database goes. But we can also convert some data types as well. And just like what you would cast with that, so just a little bit different different syntax here, as far as declaring variables go, data type goes into different place, then what you do in Java, and Python, right, you don’t have data type, so something to get used to here. But we can also convert data types, just like what you would expect to and most of your programming languages.

Casting is a standard SQL operation. So and very similar and other different languages as well. So if you wanted to cast a datatype, we can do cast and then the value that we’re trying to cast, and then as XYZ datatype, after it. So if we wanted to convert an int to a string, for example, this would be one possible way we could actually do it. The other way we can convert data types is the Convert operation convert function. This does give additional formatting options if you would like to utilize it. So convert, and then the type that we’re actually wanting to convert to, and then the value that we’re actually converting. So a little bit different syntax there. Although cast is mostly included in most standard SQL languages.

But let’s take a look at a few examples of this in action. So I just have a couple basic, a couple basic things up here. So this first statement, let me cut this out real quick. That is just my single variable, right and nothing actually comes out of it because it’s just a variable being declared. So I have some int value of type int. And I can select that, let’s see, just let’s just go and output that to our results and select if I want to do this in the query, and value and run this. So there is that column that value, the value of that variable that pops back out. And so I can utilize this anywhere, I would like to anywhere I would compare a single value inside of my query, I can use that variable there. But I can also convert that ends from an end to a string if I’d like, and a variety of other ways. But I can do the similar thing with converts. But convert is a little bit more useful in terms of especially things like date times. So if I wanted to remember what time it was, I can make a variable that stores the current date and time. And then I can convert that into a variety of formats. And if I wanted to, and so that converts feature is a little bit nicer in terms of working with dates, if you’re working with more the common data types, like INTZ, and strings and things like that.

The standard cast function works best there. So how do we utilize this inside of a query, so I can pull this code out here. So let’s declare a new variable. So salesperson ID 15. And so this is where variables start to shine a little bit more, because I can use a variable, very similar reason why I would use a variable and other things if I wanted to use a variable and more than the same value and more than one place, or just avoid hard coding, right. So if I use the salesperson ID, and multiple locations, this variable would become a little bit more important. So here is a local variable. And that we made up there. And let’s go ahead and give this a run. And now this is giving me all of the orders made by or fulfilled by salesperson ID 15. And this becomes a little bit easier to mess around with. If I wanted to change this to let’s say, salesperson five, and give us another run. And now I get all of the orders fulfilled by that particular salesperson. But there it looks like there is none for that. Let’s try there we go. So salesperson 14 had a few. The other salespeople I picked on didn’t quite have any. But this becomes a little bit easier to modify.

So my I don’t have to change my actual query itself, I can just change the value of the variable. And this becomes even more important, and we talked about stored procedures and functions in SQL later on down the road. But also note in some of my examples here, we will have statements like this, so go, and now I’m not going to cover go extensively in this lecture. Go is a batch operator in SQL. So it forces SQL SQL server here to consume all of the SQL SQL commands from the beginning or between go statements. So this executes all of this SQL code in one single batch. Because our variables exist only within the scope that they are actually defined. So if I have, for example, this so let’s go ahead and execute this code. Here. I have the results of my two queries. Here we are I have the Select up there this year, but I’m going to separate these two with go. And so each of these get executed in batch. So the first set of queries this gets executed first, and then the second batch gets executed next but Now, if I included this piece of SQL here right Ah, it no longer exists right the variable and the value is not found, because it only exists in this particular scope, right. So go can help restrict scope with variables, but go will be become a little bit more useful later down the road as we get to some more complex SQL lite transactions. So, now that we have taken a look at some variables and how to convert those variables, We’ll also explore some different data types along with that, but that will be for another video