SQL stored procedures and passing parameters
Part two 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
  2. Creating Simple Stored Procedures (this blog)
  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.

Creating Simple Stored Procedures

You can create stored procedures just by typing in the CREATE PROC command.  For example:

CREATE PROC spSimple

AS

-- pointless procedure to list all actors from a database

SELECT * FROM tblActor

This page looks at this command in detail - but let's begin with a way NOT to create procedures.

There's a good reason NOT to us sp_ as a prefix for your stored procedure.

How Not to Create Stored Procedures - Using the Menu

An obvious (but misguided) way to create stored procedures would be by right-clicking on the Stored Procedures category in SQL Server Management Studio:

Creating a stored procedure

You can right-click as shown and choose to create a new stored procedure.

 

What's wrong with this approach?  Well, it brings up a complicated, confusing template:

Part of Stored Procedure template

The start of the stored procedure template - how to confuse newcomers to SQL!

You're better off writing your own code, believe me, which is what we'll look at now.

The Syntax for Creating a Basic Stored Procedure

Here's how to create your first stored procedure.  Firstly, it's a good idea to make sure that you're creating it in the right database:

-- it's a good idea to make sure you're in the right database

USE Movies

GO

Now you need to tell SSMS (SQL Server Management Studio) that you're creating a stored procedure:

-- the first two words you need

CREATE PROC

The next thing that you need is to give your procedure a name.  Commonly these begin with sp, but you can use any name you like:

-- by convention procedure names begin with SP

CREATE PROC spListActors

The next word you need is the one most commonly forgotten: AS.  This separates the declaration of the stored procedure from what it does:

CREATE PROC spListActors

-- pointless but necessary word!

AS

I have no idea why this word is necessary, but it is - the best thing to do is just live with the rule and try to remember it.

The rest of the stored procedure can do anything which SQL can do: inserting, updating and deleting rows, for example.  To keep things simple, we'll just display the actors in our database:

CREATE PROC spListActors

AS

-- display list of actors

SELECT

ActorName,

ActorGender

FROM

tblActor

You can optionally type in the word GO at the end of your procedure to finish this batch of commands, but it isn't necessary normally.

Running the Query to Create the Stored Procedure

Having typed in the command above to create a stored procedure, you now need to tell SSMS to go away and do this.  To do this, just run your query:

Creating a stored procedure

You can click anywhere in your query and press F5, or click on the Execute button as shown here.

 

What this will do - if you haven't made any syntax errors - is run successfully:

Run OK message

All that you get for your effort - a message telling you that the command to create a stored procedure has run successfully.

 

It's important to realise at this point that you haven't run the stored procedure; instead, you've run the command to create it!

Getting your Stored Procedure to Show Up in SSMS

Disconcertingly, you won't immediately see your stored procedure in the list:

List of stored procedures

Where is your new stored procedure?

 

To get SSMS to display your new procedure, refresh the list:

Refreshing list of stored procedures

Right-click on the Stored Procedures category and choose to refresh it, as shown here.

 

Phew!

The new stored procedure

You should now see your new stored procedure (if, that is, you added it to the right database!).

 

The Perils of Trying to Create the Same Stored Procedure Twice

If you run the SQL to create the stored procedure spListActors above a second time, you'll get this error message:

Error on creating sp

You can't create the same procedure twice!

In this case, the thing to do would be to change the word CREATE to ALTER before running the SQL again, as described in a later part of this blog on modifying stored procedures.

 

Having created your stored procedure, the next thing to do is find out how to run it!

This blog has 0 threads Add post