Before we go any further, we need to learn about a very powerful component of SQL server. The variable. If you have programmed in any programming language before, you already know what a variable is. If you haven’t, think of it as a sticky note that has space for you to write one thing. This sticky note has a predetermined lifespan, during which you can erase what it says and replace it with something else. You can ask to see what’s on the sticky note at any time, too.
SQL supports tons of variables. We're not going to explain everyone in all its detail. We're going to focus on the ones that matter most. We'll get into the DECALRE statement in just a bit. For starters, take a look at the different available types.
These four variables are the most commonly used ones. We'll take a look at two very simple examples of how to use variables and then build on this as we progress.
The DECLARE statement is used to initialize a variable. This tells SQL we're ready to start using a variable. We'll look at making a number and also a string value since there are some minor differences.
Here's how we create a numeric variable:
DECLARE @Age INT
From this point out, we can use the word @Age anywhere that we'd normally use a literal value. Let's look at how we create a string, or alphanumeric, variable before we go further.
DECLARE @FirstName VARCHAR(50)
You'll notice a slight difference here. What is that "(50)" at the end of the declaration? This tells SQL how much space to make for this variable. This declaration makes a variable that can hold up to fifty characters. Most variables are declared like the INT. We just put them out there and SQL does the rest. But there are a few that have additional parameters. We'll talk about these as we use them.
Now that we've created variables, we need to know how to fill them with something. The SET statement is the key to putting something into your variable to use later.
SET @FirstName = 'Jesse'
SET @Age = '30'
We're almost ready to start playing with variables. There is just one more thing we need to learn about: how to take a quick peek inside them.
The PRINT statement has numerous uses that we'll be seeing going forward. You've probably noticed whenever you run things in SQL server, that messages come back, saying things like "10 row(s) inserted" and other messages. You can put your own messages into the SQL output using the PRINT statement.
Try running the following in a new window:
PRINT 'Hello World!'
This might seem pointless, but sometimes it's very useful to know just what SQL is doing when running a long query. It's also useful to take a quick peek inside a variable and see what it's doing. We'll see this in our next step.
Now that we understand the DECLARE, SET and PRINT statements, we can start fiddling around a bit with variables. Let's take a look at what happens in a variable as we run different statements.
PRINT "Making Variable."
DECLARE @Num INT
PRINT "The variable's value is:"
PRINT "Setting to 7."
SET @Num = 7
PRINT "The value is:"
Run this SQL, you'll see the following:
The variable's value is:
Setting to 7.
The value is:
As you can see, the variable was created with a value of zero. Our SET statement changed it to seven. Now, try just running the statement PRINT @Num by itself. You'll get an error. Why? The variable only exists as long as we have SQL running. In the example above, the variable only exists from the time we run the DECLARE until the time that the time the last SQL statement finishes.
Now let's see how to use a variable in a query. We still aren't getting into the real power of variables but after this lesson we should understand them a little better.
DECLARE @Name VARCHAR(50)
SET @Name = 'Jesse'
FROM STUDENTS WHERE FirstName = @Name
FROM TEACHERS WHERE FirstName = @Name
As we can see, the variable serves the same purpose as the statement FirstName = "Jesse". The only difference is we can change it in the SET statement and both queries will change. When we need to use the same value in many places, a variable is a handy way to do it.
We'll get more into variables as we move ahead. This lesson should have helped you to understand the basics of setting them up and utilizing them within a query.
Tainyan staff who teach SQL.