BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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
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:
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:
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.
- Variables in SQL
- Uses of Variables in SQL (this blog)
- Tricks with Variables in SQL