BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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!
- Stored Procedures in SQL
- Creating Simple Stored Procedures
- Running Stored Procedures
- Modifying or Changing a Stored Procedure
- Passing Parameters to Stored Procedures
- 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:
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 <= @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!
- Stored Procedures in SQL
- Creating Simple Stored Procedures
- Running Stored Procedures
- Modifying or Changing a Stored Procedure
- Passing Parameters to Stored Procedures
- A Worked Example of a Longer Stored Procedure (this blog)
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.
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.
Thank you for the clarification and for your quick response Andy!