Declaring and using SQL variables
Part one of a three-part series of blogs

Variables in SQL always begin with @, and allow you to program more efficiently. Learn the tips and tricks in this short series of blogs!

  1. Variables in SQL (this blog)
  2. Uses of Variables in SQL
  3. Tricks with Variables in SQL

This blog is part of our full SQL tutorial.  The real-world equivalent (classroom courses with small groups) can be found at our SQL training pages.

Posted by Andy Brown on 03 January 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Variables in SQL

A variable is a space in memory in which you can store a single piece of information (note that I'll cover SQL global variables in a separate blog).

This blog is written for versions of SQL Server 2008 R2 or later - a few of the tricks won't work in earlier versions (although most will).

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 some variables


-- the name of a company

DECLARE @CompanyName varchar(50)


-- when a course starts

DECLARE @StartDate datetime


-- someone's age in years

DECLARE @Age int


-- the cost of something in pounds/pence

DECLARE @Cost decimal(5,2)

You can declare several variables on the same line (although I personally don't like the idea):

-- declare 4 variables

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:

-- create a variable and assign a value to it

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:

-- create a variable and assign a value to it

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:

  1. It's the ANSI standard for SQL; and
  2. 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:

-- create a variable and assign a value to it

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:

-- set counter

DECLARE @counter int = 0


-- subtract 1 then add 3

SET @counter -= 1

SET @counter += 3


-- print result

PRINT 'Counter = ' + CAST(@counter AS varchar(10))

This would give the following output:

Output from counter code

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:

-- create and set a variable

DECLARE @Answer int = 42


-- start new batch



-- show value of variable

SELECT @Answer

Here's the error you'd get if you ran the SQL above:

Message 137 - scalar variable not declared

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 has 0 threads Add post