562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls 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
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!
This blog is part of a complete SQL Server tutorial.
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.
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(
-- create a table of years
DECLARE @tblYears TABLE (
YearId int IDENTITY(1,1) PRIMARY KEY,
-- populate it with n years
DECLARE @year int
SET @year = @StartYear
WHILE @year <= @EndYear
-- insert into table this year
INSERT INTO @tblYears (
) VALUES (
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 = (
SELECT COUNT(*) FROM tblActor
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!
|Parts of this blog|
|When:||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.
|When:||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.
25 Aytoun Street