Declaring and using SQL variables
Part three 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
  2. Uses of Variables in SQL
  3. 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:

Detalis of most expensive film

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:

Number of films, and ids

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!

  1. Variables in SQL
  2. Uses of Variables in SQL
  3. Tricks with Variables in SQL (this blog)
This blog has 0 threads Add post