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!

  1. Variables in SQL
  2. Uses of Variables in SQL (this blog)
  3. 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

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.

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)

AS

-- 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)

AS

-- 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)

)

AS

 

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

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