SQL stored procedures and passing parameters
Part six of a six-part series of blogs

Stored procedures are programs in SQL which allow you to pass parameters to queries and manipulate sets of rows in tables. Find out how to write them with this online training blog!

  1. Stored Procedures in SQL
  2. Creating Simple Stored Procedures
  3. Running Stored Procedures
  4. Modifying or Changing a Stored Procedure
  5. Passing Parameters to Stored Procedures
  6. A Worked Example of a Longer Stored Procedure (this blog)

This blog is part of a complete SQL Server tutorial, and is also referenced from our ASP.NET online training blog.

Posted by Andy Brown on 24 May 2012

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.

A Worked Example of a Longer Stored Procedure

The rest of this blog explains how you can create stored procedures, and how you would pass parameters to them.  I thought I'd finish 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:

Number of directors and actors

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

)

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

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! 

This blog has 0 threads Add post