SQL stored procedures and passing parameters
Part one 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 (this blog)
  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 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.

Stored Procedures in SQL

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 blog that you know how to create basic SQL statements to list columns and rows (at some stage we'll even write a tutorial online about this, and link to this here).

Views versus Stored Procedures

Consider the following SQL code:

CREATE VIEW vwFilmsList

AS

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

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:

Running a view

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

AS

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:

Stored procedure in database

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:

  1. Create a temporary table of all of the films which were released in the year 2000.
  2. Go through this temporary table calculating the profitability of each film.
  3. Cross-reference the results to another table containing all of the costs for films made in the US for 2000.
  4. 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. 

This blog has 0 threads Add post