This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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! 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.

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!

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

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!

Parts of this blog
Variables in SQL Uses of Variables in SQL Tricks with Variables in SQL (this blog)

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Tricks with Variables in SQL

Reading Column Values into Variables

Accumulating Values for a Field

Head office

London

Manchester