BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Variables in SQL always begin with @, and allow you to program more efficiently. Learn the tips and tricks in this short series of blogs!
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.
Tricks with Variables in SQL
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:
-- create variables to hold column values
DECLARE @FilmName varchar(100)
DECLARE @Oscars int
DECLARE @RunTime int
-- read the most expensive film into these variables
SELECT TOP 1
@FilmName = FilmName,
@Oscars = FilmOscarWins,
@RunTime = FilmRunTimeMinutes
-- show results
@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:
-- variables to hold info
DECLARE @TotalOscars int = 0
DECLARE @FilmIds varchar(MAX) = ''
-- loop through films winning lots of Oscars
@TotalOscars = @TotalOscars + FilmOscarWins,
-- add on a comma for all but first film
@FilmIds = @FilmIds +
WHEN len(@FilmIds) = 0 THEN ''
END + CAST(FilmId AS varchar(10))
FilmOscarWins > 7
-- show results accumulated
@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 blog use variables, but that's the end of the theory about them!