562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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!
This pages lists some non-intuitive tricks which work when using variables in SQL.
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!
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!
|Parts of this blog|
25 Aytoun Street