This page has 0 threads | Add post

Stored Procedures in SQL

Written by Andy Brown

In this tutorial

Views versus Stored Procedures
The Equivalent Stored Procedure
Reason 1 for Stored Procedures - Passing Parameters
Reason 2 for Stored Procedures - Programming

If you can write SQL, you can write stored procedures - the first question to consider perhaps is why you would want to! To do this, let's start with a simpler concept: the view.

I'm going to assume throughout this tutorial that you know how to create basic SQL statements to list columns and rows

Views versus Stored Procedures

Consider the following SQL code:

CREATE VIEW vwFilmsList

SELECT

-- show film name/Oscars

FilmName,

FilmOscarWins AS Oscars

FROM

tblFilm

This will create a view called vwFilmsList, which you can then run at any time. For example, you could list out the films which won more than 10 Oscars like this:

-- show films winning

-- more than 10 Oscars

SELECT

FROM

vwFilmsList

WHERE

Oscars>10

This will produce the following results:

The 2 films winning more than 10 Oscars.

You can also run the view at any time by right-clicking on it and choosing to select all of its rows:

You can right click on your view and choose to show its first 1,000 rows (ie all of them!).

So far, so good. What puzzled me when I was first learning stored procedures is - why would I ever want to create them, when views were so much simpler?

The Equivalent Stored Procedure

You could accomplish exactly the same thing by creating and then running a stored procedure:

CREATE PROC spListFilms

SELECT

-- show film name/Oscars

FilmName,

FilmOscarWins AS Oscars

FROM

tblFilm

You could then run this stored procedure as follows (you don't even need the word EXEC in the command):

-- run our stored procedure

EXEC spListFilms

You can see your stored procedure listed as follows:

The stored procedures that you create are listed in the programmability section of your database in SQL Server Management Studio.

However, you can't directly select records from the stored procedure, nor can you right-click on it to list its rows. So I repeat the question: why bother?

Reason 1 for Stored Procedures - Passing Parameters

The biggest reason for creating stored procedures is that it enables you to pass parameters. For example, suppose that you want to list out all of the films containing a given string of text. Then you could pass this as a parameter:

-- run our stored procedure to list

-- all films about Shrek

spListFilms 'Shrek'

We'd need to modify the stored procedure to accept this parameter, of course. Or how about being able to list out all of the films released between two given dates:

-- list films made since the start of the century

spListFilmsByDate '01/01/2000', GetDate()

One of the great benefits of passing parameters is that it provides a foolproof way of avoiding SQL injection attacks. Both creating parameters and SQL injection attacks are covered in much more detail later in this blog.

Reason 2 for Stored Procedures - Programming

For our example at the start of this page, the stored procedure didn't offer any advantages over the view. But how about if we wanted to write a program to:

Create a temporary table of all of the films which were released in the year 2000.
Go through this temporary table calculating the profitability of each film.
Cross-reference the results to another table containing all of the costs for films made in the US for 2000.
Find any discrepanicies and add these to a table of errors for subsequent display on a website.

A view can only display data; a stored procedure can perform a full range of programming commands. This is indeed why they're called stored procedures: they're programming procedures which are stored within SQL Server.

A quick plug: if you want to learn the full range of commands possible when programming stored procedures, sign up to our two-day Advanced SQL course.

Now that I've explained - I hope - why stored procedures are so much more powerful than simple views, let's look at how to create them, starting with the basics.

Previous
tutorial

Trapping errors and debugging

Previous
part

Top of page

Next
part

Creating Simple Stored Procedures

Next
tutorial

User-defined functions

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on SQL