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
Search our website
We also send out useful tips in a monthly email newsletter ...
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 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. |
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
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!
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!
Parts of this blog |
---|
|
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.