This page has 0 threads | Add post

Wise Owl guides and tutorials

Creating Simple Stored Procedures

Written by Andy Brown

In this tutorial

How Not to Create Stored Procedures - Using the Menu
The Syntax for Creating a Basic Stored Procedure
Running the Query to Create the Stored Procedure
Getting your Stored Procedure to Show Up in SSMS
The Perils of Trying to Create the Same Stored Procedure Twice

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

CREATE PROC spSimple

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

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:

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

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!

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

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

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:

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:

Where is your new stored procedure?

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

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

Phew!

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:

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! Don't forget that you can learn how to write stored procedures on our advanced SQL course, or visit our SQL training home page for more training resources.

Previous
tutorial

Trapping errors and debugging

Previous
part

Stored Procedures in SQL

Top of page

Next
part

Running 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

Stored procedures

Creating Simple Stored Procedures

How Not to Create Stored Procedures - Using the Menu

The Syntax for Creating a Basic Stored Procedure

Running the Query to Create the Stored Procedure

Getting your Stored Procedure to Show Up in SSMS

The Perils of Trying to Create the Same Stored Procedure Twice

Head office

London

Manchester