A variable is a space in memory in which you can store a single piece of information (note that I've covered SQL global variables in a separate part of this tutorial).
Declaring Variables
You can use the word DECLARE to create a variable for you to use. The syntax is:
DECLARE @VariableName AS datatype
The keyword AS is optional; I miss it out for variable declarations (which makes me Mr. Inconsistent, as readers of other parts of this SQL training series will realise).
Variable names can not contain spaces, and must start with @. You can see the full list of data types that you can use in SQL in this separate blog. Here are some examples of variables:
DECLARE @CompanyName varchar(50)
DECLARE @StartDate datetime
DECLARE @Age int
DECLARE @Cost decimal(5,2)
You can declare several variables on the same line (although I personally don't like the idea):
DECLARE @CompanyName varchar(50), @StartDate datetime,
@Age int, @Cost decimal(5,2)
Assigning values to variables - SET versus SELECT
Once you've created some variables, you can assign values to them in one of three main ways. The easiest one is to use SET:
DECLARE @CompanyName varchar(50)
SET @CompanyName = 'Wise Owl'
This will store the text string Wise Owl in the string variable called @CompanyName.
Note for VBA programmers - the word SET has nothing to do with object variables, and is compulsory!
Alternatively, you can use the SELECT keyword:
DECLARE @CompanyName varchar(50)
SELECT @CompanyName = 'Wise Owl'
So what's the difference? Practically none is the answer. If you're interested, Google SQL variables SET versus SELECT. I always use SET, because:
It's the ANSI standard for SQL; and
It seems to me to fit in better with what is essentially a programming command.
Finally, you can declare a variable and assign a value to it in the same line of code:
DECLARE @CompanyName varchar(50)= 'Wise Owl'
Incrementing and decrementing integer variables
A common requirement in programming is to add or subtract one to/from a variable. There's a short-hand way to do this in SQL:
DECLARE @counter int = 0
SET @counter -= 1
SET @counter += 3
PRINT 'Counter = ' + CAST(@counter AS varchar(10))
This would give the following output:
The final value for the counter is 2.
The Scope of Variables
Variables have a very short life! In fact, the life of a variable is restricted to a single batch of SQL statements. This SQL would give an error:
DECLARE @Answer int = 42
GO
SELECT @Answer
Here's the error you'd get if you ran the SQL above:
SQL can't find the variable you've declared two lines earlier!
If you remove the GO statement, the above SQL would run and display the number 42.
Why you might use variables
So now that we've seen how to create variables, why might you use them? My usual reason is to hold the number of rows returned from a SQL statement, but I've given a few examples and case studies in the next part of this blog.
This blog page gives a couple of examples of when you might use variables in SQL. They are rarely necessary, but make your SQL much easier to write and read.
Example - Function to Extract Someone's First Name
Suppose that you want to write a function to give actors' first names:
SELECT
ActorName,
dbo.fnFirstName(ActorName) AS 'First Name',
dbo.fnLastName(ActorName) AS 'Last Name'
FROM
tblActor
ORDER BY
ActorName
This should give something like this:
The results from the above query, once we've written the two functions!
This query makes use of two functions, to extract the first and last names from a full name. Here is the one to get the first name:
CREATE FUNCTION [dbo].[fnFirstName](
@PersonName varchar(100)
)
RETURNS varchar(100)
AS
BEGIN
DECLARE @FirstSpace int = charindex(' ',@PersonName)
DECLARE @FirstName varchar(100)
IF @FirstSpace = 0
BEGIN
SET @FirstName = @PersonName
END
ELSE
SET @FirstName = substring(@PersonName,1,@FirstSpace-1)
RETURN @FirstName
END
This makes use of two variables as follows:
Variable | Type | Notes |
|---|
@FirstSpace | int | Holds the position of the first space within the actor's name. |
@FirstName | varchar(100) | Holds the accumulated first name, to be returned from the function. |
Note that it would be possible to write this function without using variables, but the result would be much harder to read.
For the sake of completenes, here's a version of the fnLastName function (I'm sure it could be written more elegantly), which will work even for troublesome actors like Samuel L. Jackson and Jamie Lee Curtis:
CREATE FUNCTION fnLastName(
@PersonName varchar(100)
)
RETURNS varchar(100)
AS
BEGIN
DECLARE @LastSpace int = charindex(' ',reverse(@PersonName))
DECLARE @LastName varchar(100)
IF @LastSpace = 0
BEGIN
SET @LastName = @PersonName
END
ELSE
SET @LastName = substring(@PersonName,len(@PersonName)-@LastSpace+2,@LastSpace-1)
RETURN @LastName
END
Example - Holding Row Counts in Variables
If a SQL SELECT statement returns a single value, you can hold this in a variable. Suppose you want to create a stored procedure to show the number of films, actors and directors containing a given string of text:
This would return (for the Wise Owl movies database) the following:
Out of interest, the director is Alex Proyas.
Here is a stored procedure which would solve this:
CREATE PROC spFindText(
@what varchar(100)
)
AS
DECLARE @numActors int
DECLARE @numDirectors int
DECLARE @numFilms int
SET @numActors = (
SELECT COUNT(*) FROM tblActor
WHERE ActorName like '%' + @what + '%'
)
SET @numDirectors = (
SELECT COUNT(*) FROM tblDirector
WHERE DirectorName like '%' + @what + '%'
)
SET @numFilms = (
SELECT COUNT(*) FROM tblFilm
WHERE FilmName like '%' + @what + '%'
)
SELECT
@numActors AS 'Actors found',
@numDirectors AS 'Directors found',
@numFilms AS 'Films found'
As always, I'm sure there are shorter ways to write this, but would they be as clear to read as the above?
Having looked at a couple of examples of how you might use variables, it's time now to look at some less obvious tricks you can use.
This pages lists some non-intuitive tricks which work when using variables in SQL.
Reading Column Values into Variables
Provided that a SELECT statement returns a single row of data, you can read the values into variables:
Our SQL will show the name, Oscars and length for the most expensive film in the database.
Here is a query which would accomplish this:
DECLARE @FilmName varchar(100)
DECLARE @Oscars int
DECLARE @RunTime int
SELECT TOP 1
@FilmName = FilmName,
@Oscars = FilmOscarWins,
@RunTime = FilmRunTimeMinutes
FROM
tblFilm
ORDER BY
FilmBudgetDollars DESC
SELECT
@FilmName AS 'Most expensive film',
@Oscars AS 'Oscars won',
@RunTime AS 'Length in minutes'
I admit I've rarely - if ever - used this technique!
Accumulating Values for a Field
Of rather more use, perhaps, you can accumulate values (whether text or numbers) in variables within a SELECT statement:
DECLARE @TotalOscars int = 0
DECLARE @FilmIds varchar(MAX) = ''
SELECT
@TotalOscars = @TotalOscars + FilmOscarWins,
@FilmIds = @FilmIds +
CASE
WHEN len(@FilmIds) = 0 THEN ''
ELSE ','
END + CAST(FilmId AS varchar(10))
FROM
tblFilm
WHERE
FilmOscarWins > 7
SELECT
@TotalOscars AS 'Total Oscars',
@FilmIds AS 'Film ids'
This query would return the following for the Wise Owl movies database:
The query has accumlated the total number of films, and created a comma-separated list of their ids.
The above technique can be used to avoid using cursors to loop over the rows in a table.
Many of the other parts of this SQL tutorial use variables, but that's the end of the theory about them!
