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 two 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 (this blog) Tricks with Variables in SQL 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 two 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 (this blog)
Tricks with Variables in SQL

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

Uses of Variables in SQL

This blog page gives a couple of examples of when you might use variables in SQL. They are rarely necessary, but make your SQL much easier to write and read.

Example - Function to Extract Someone's First Name

Suppose that you want to write a function to give actors' first names:

SELECT

ActorName,

dbo.fnFirstName(ActorName) AS 'First Name',

dbo.fnLastName(ActorName) AS 'Last Name'

FROM

tblActor

ORDER BY

ActorName

This should give something like this:

List of actors with first and last names

The results from the above query, once we've written the two functions!

This query makes use of two functions, to extract the first and last names from a full name. Here is the one to get the first name:

CREATE FUNCTION [dbo].[fnFirstName](

@PersonName varchar(100)

)

RETURNS varchar(100)

-- function returns someone's first name

BEGIN

-- find the position of the first space

DECLARE @FirstSpace int = charindex(' ',@PersonName)

DECLARE @FirstName varchar(100)

IF @FirstSpace = 0

BEGIN

-- no space found, so return entire name

SET @FirstName = @PersonName

END

ELSE

-- if a space found, pick out characters before it

SET @FirstName = substring(@PersonName,1,@FirstSpace-1)

-- return the first name

RETURN @FirstName

END

This makes use of two variables as follows:

Variable	Type	Notes
@FirstSpace	int	Holds the position of the first space within the actor's name.
@FirstName	varchar(100)	Holds the accumulated first name, to be returned from the function.

Note that it would be possible to write this function without using variables, but the result would be much harder to read.

For the sake of completenes, here's a version of the fnLastName function (I'm sure it could be written more elegantly), which will work even for troublesome actors like Samuel L. Jackson and Jamie Lee Curtis:

CREATE FUNCTION fnLastName(

@PersonName varchar(100)

)

RETURNS varchar(100)

-- function returns someone's last name

BEGIN

-- find the position of the last space

DECLARE @LastSpace int = charindex(' ',reverse(@PersonName))

DECLARE @LastName varchar(100)

IF @LastSpace = 0

BEGIN

-- no space found, so return entire name

SET @LastName = @PersonName

END

ELSE

-- if a space found, pick out characters after it

SET @LastName = substring(@PersonName,len(@PersonName)-@LastSpace+2,@LastSpace-1)

-- return the last name

RETURN @LastName

END

Example - Holding Row Counts in Variables

If a SQL SELECT statement returns a single value, you can hold this in a variable. Suppose you want to create a stored procedure to show the number of films, actors and directors containing a given string of text:

-- show number of things containing X

spFindText 'X'

This would return (for the Wise Owl movies database) the following:

Number of actors, etc, containing given text

Out of interest, the director is Alex Proyas.

Here is a stored procedure which would solve this:

CREATE PROC spFindText(

@what varchar(100)

)

-- finds how many actors, films and directors contain this text

DECLARE @numActors int

DECLARE @numDirectors int

DECLARE @numFilms int

-- find number of actors

SET @numActors = (

SELECT COUNT(*) FROM tblActor

WHERE ActorName like '%' + @what + '%'

)

-- find number of directors

SET @numDirectors = (

SELECT COUNT(*) FROM tblDirector

WHERE DirectorName like '%' + @what + '%'

)

-- find number of films

SET @numFilms = (

SELECT COUNT(*) FROM tblFilm

WHERE FilmName like '%' + @what + '%'

)

-- show results

SELECT

@numActors AS 'Actors found',

@numDirectors AS 'Directors found',

@numFilms AS 'Films found'

As always, I'm sure there are shorter ways to write this, but would they be as clear to read as the above?

Having looked at a couple of examples of how you might use variables, it's time now to look at some less obvious tricks you can use.

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

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

Uses of Variables in SQL

Example - Function to Extract Someone's First Name

Example - Holding Row Counts in Variables

Head office

London

Manchester