This page has 0 threads | Add post

A Worked Example of a Longer Stored Procedure

Written by Andy Brown

In this tutorial

The Aim of the Stored Procedure
The Stored Procedure

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.

The Aim of the Stored Procedure

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!

The Stored Procedure

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

)

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

)

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!

Previous
tutorial

Trapping errors and debugging

Previous
part

Returning Values from Stored Procedures in SQL

Top of page

Next
part

Next
tutorial

User-defined functions

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on SQL

Stored procedures

A Worked Example of a Longer Stored Procedure

The Aim of the Stored Procedure

The Stored Procedure

Head office

London

Manchester