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