Single Value Variables
Published: Jan 06, 2014
These are incredibly useful when writing complex T-SQL or stored procedures etc. as they are very versatile and simple to use. They are also only active and present during the execution of your code. They are automatically dropped and tidied up once execution finishes.

Effectively they are storage in which you can place a value to be used and manipulated throughout your code and refer to it by the variable name.

The best way to explain this is with a simple example. Here we will create a variable called “myNumber” and assign it the value 2. This can then be used in a simple select statement…

declare @myNumber int
@myNumber = 2

select @myNumber * 10

Note the syntax used here… to create a variable we use DECLARE. A variable must always have a name starting with @, and it must also always have a datatype. For example:

declare @myString varchar(20)
set @myString = 'A string of text'

select @myString

Or if you want to declare multiple variables:

declare @myString varchar(20), @myString2 varchar(10)
set @myString = 'A string of text'
set @myString2 = ' as a demo.'

select @myString + @myString2

There are also other ways in which to set a value to a variable. If you are using SQL 2008 upwards then you can set the value as part of the declaration:

declare @myNumeric numeric(3, 1) = 5.0, @myInt int = 2

select @myNumeric / @myInt

You can also use a SELECT statement:

declare @myDate datetime, @now datetime

@myDate = '2001-01-01', @now = CURRENT_TIMESTAMP

select DATEDIFF(dd, @myDate, @now)

A SELECT can also be used to assign straight from a table:

declare @maxDate date

select @maxDate = MAX(orderDate)
from dbo.orders

Once assigned these can be used in a variety of ways which you’ll soon discover as you play with them, but the most common are in a where clause, for creating loops, and in dynamic SQL:

Where Clause:

declare @myInt int = 1

select *
from inventedTable
where id = @myInt

While Loop:

declare @counter int = 1

while @counter <= (select MAX(id) from myTable)
set miscValue = RAND() * miscValue
where id = @counter

set @counter += 1 -- NOTE: use set @counter = @counter + 1 for SQL 2005

Dynamic T-SQL:

declare @mySQL nvarchar(200)

select @mySQL = 'select top 10 * from randomTable where name = '''
+ (select name from nameTable where id = 1) + ''''

exec sp_executeSQL @mySQL

This should give you enough to work with if you wish to have a play around and see what a variable is, how it works, and what you can do with it and there will also be plenty of code samples and other articles within this website which will utilise variables and let you see them in action.

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London



Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron