This page has 1 thread | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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! 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.

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!

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.

Posted by Andy Brown on 24 May 2012

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

)

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

Parts of this 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)

Some other pages relevant to the above blogs include:

This blog has 1 thread

Add a new post

From:	Phani459
When:	29 May 19 at 02:34

Reply to Phani459

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.

From:	Andy B
When:	29 May 19 at 08:58

Reply to Andy B

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.

From:	Phani459
When:	31 May 19 at 01:09

Reply to Phani459

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

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

A Worked Example of a Longer Stored Procedure

The Aim of the Stored Procedure

The Stored Procedure

Head office

London

Manchester