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!
- Variables in SQL
- Uses of Variables in SQL
- Tricks with Variables in SQL (this blog)
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.
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
FROM
tblFilm
ORDER BY
FilmBudgetDollars DESC
-- show results
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:
-- variables to hold info
DECLARE @TotalOscars int = 0
DECLARE @FilmIds varchar(MAX) = ''
-- loop through films winning lots of Oscars
SELECT
@TotalOscars = @TotalOscars + FilmOscarWins,
-- add on a comma for all but first film
@FilmIds = @FilmIds +
CASE
WHEN len(@FilmIds) = 0 THEN ''
ELSE ','
END + CAST(FilmId AS varchar(10))
FROM
tblFilm
WHERE
FilmOscarWins > 7
-- show results accumulated
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 blog use variables, but that's the end of the theory about them!
- Variables in SQL
- Uses of Variables in SQL
- Tricks with Variables in SQL (this blog)