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.

Variable Types

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.

Variable Name Example Data PurposeSample Declare
int 15 Stores a numeric value. DECLARE @Quantity INT
varchar'415 Fake Street'Stores an alphanumeric string of variable length. Maximum length is defined in declare.DECLARE @Address VARCHAR(50)
datetime'5/20/2012 5:15pm'Stores a date or time or both.DECLARE @BirthDay DATETIME
money54.23Stores a numeric value that uses special rules for currency (i.e. you cant divide smaller than a penny).DECLARE @Price money

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

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.

The SET Statement

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

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!'

The output?

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.

Playing With Variables

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 @Num
PRINT "Setting to 7."
SET @Num = 7
PRINT "The value is:"
PRINT @Num

Run this SQL, you'll see the following:

Making Variable.
The variable's value is:
0
Setting to 7.
The value is:
7

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.

Using a Variable in a Query

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'

SELECT *
FROM STUDENTS WHERE FirstName = @Name

SELECT * 
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.



author's photo

Author : SQL Tutor

Tainyan staff who teach SQL.

COMMENTS
blog comments powered by Disqus
Contributors
Categories