Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
545 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
Written by Andy Brown
In this tutorial
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.
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.
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.
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!
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!).
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.
You can learn more about this topic on the following Wise Owl courses:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.