This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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! Variables in SQL (this blog) Uses of Variables in SQL 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.

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!

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

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:

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:

-- 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:

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.

Parts of this blog
Variables in SQL (this blog) Uses of Variables in SQL Tricks with Variables in SQL

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Variables in SQL

Declaring Variables

Assigning values to variables - SET versus SELECT

Incrementing and decrementing integer variables

The Scope of Variables

Why you might use variables

Head office

London

Manchester