WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
30 years in business
Wise Owl Training
30 years in business
See 519 reviews for our classroom and online training
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



-- create a table of years



YearNumber int,

NumberDirectors int,

NumberActors int


-- populate it with n years

DECLARE @year int

SET @year = @StartYear

WHILE @year <= @EndYear


-- insert into table this year

INSERT INTO @tblYears (









SET @year = @year + 1


-- set how many directors born in each year




NumberDirectors = (

SELECT COUNT(*) FROM tblDirector

WHERE Year(DirectorDob) = YearNumber


-- set how many actors born in each year




NumberActors = (


WHERE Year(ActorDob) = YearNumber


-- return results





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 1 thread Add post
29 May 19 at 02:34

Can you please explain the elaborate on the line of coce  below which is referred to in the above code snippet. This is the first time I'm seeing this operator.

WHILE @year <=>

Incase there is already a tutorial on this, can you please point me to the related article. Thanks in advance.

29 May 19 at 08:58

Thanks for reporting this!  There is no such operator - just a recurring problem in some of our blogs with < and > signs, which HTML authors will know well.  I've now corrected this, and the procedure should make a bit more sense.  

31 May 19 at 01:09

Thank you for the clarification and for your quick response Andy!