Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
548 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers 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 ...
Written by Andy Brown
In this tutorial
I thought I'd finish this tutorial with a longer worked example, to illustrate that stored procedures can do much more than just select data.
This page assumes a fair bit of knowledge of SQL, but SQL novices will be able to get the gist of what's possible, I hope.
I want to write a stored procedure which allows me to show the number of actors and directors born in a given range of years. I could run this with parameters:
-- show number of directors and actors
-- born in each year
spShowYears 1970, 1980
The idea is that this would return data like this:
This shows, for example, that in 1974 there was 1 director and 7 actors born in our database.
I'm aware that you could probably accomplish this with a single, very complicated SQL statement; but I'm a great believer in breaking up complex problems to make them understandable by my poor human brain!
Here is a solution to the above problem. I'm showing this really because I don't want to leave the lingering impression that all that stored procedures can do is select data:
CREATE PROC spShowYears(
@StartYear int,
@EndYear int
)
AS
-- create a table of years
DECLARE @tblYears TABLE (
YearId int IDENTITY(1,1) PRIMARY KEY,
YearNumber int,
NumberDirectors int,
NumberActors int
)
-- populate it with n years
DECLARE @year int
SET @year = @StartYear
WHILE @year <= @EndYear
BEGIN
-- insert into table this year
INSERT INTO @tblYears (
YearNumber,
NumberDirectors,
NumberActors
) VALUES (
@year,
0,
0
)
SET @year = @year + 1
END
-- set how many directors born in each year
UPDATE
@tblYears
SET
NumberDirectors = (
SELECT COUNT(*) FROM tblDirector
WHERE Year(DirectorDob) = YearNumber
)
-- set how many actors born in each year
UPDATE
@tblYears
SET
NumberActors = (
SELECT COUNT(*) FROM tblActor
WHERE Year(ActorDob) = YearNumber
)
-- return results
SELECT
*
FROM
@tblYears
Like I said, I'm sure there are simpler ways to achieve this, but it does give an idea of what's possible with stored procedures!
You can learn more about this topic on the following Wise Owl courses:
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 2024. All Rights Reserved.