A SQL Server stored procedure, sometimes referred to as a stored proc or procedure, allows you to write one or more TSQL statements and store them within your database so that they can be called over and over again without having to write that code a again. This article shows you how to create a stored procedure for the Microsoft SQL Server database.
This article is based on Learn SQL Server 2012 in a Month of Lunches, to be published in Fall 2012. This eBook is available through the Manning Early Access Program (MEAP). Download the eBook instantly from manning.com. All print book purchases include free digital formats (PDF, ePub and Kindle). Visit the book’s page for more information based on Learn SQL Server 2012 in a Month of Lunches. This content is being reproduced here by permission from Manning Publications.
Author: Grant B. Fritchey
Procedures are extremely powerful. They can be extremely simple with only a single statement against a single table. You can have more than one statement in a procedure so they can grow to become quite complicated. The best procedures focus on a single task. Stored procedures also allow values to be passed to them through a construct called a parameter. Parameters can be a variety of different data types in support of the data types that you have available within your tables. This means that a stored procedure can be reused, over and over, to run commands for different values. Let’s get started creating some stored procedures now.
Stored procedures don’t offer a graphical user interface to create them. They are strictly created only through the use of TSQL. The syntax is roughly what you might expect. Let’s start off with a simple procedure that will select data from a single table:
CREATE PROCEDURE Management.GetAddressInfo
AS
SELECT *
FROM Management.Address;
We then tell SQL Server that we’re creating a PROCEDURE. The schema and name are supplied. The schema is important because it can affect how security settings are made on the procedure. It’s generally best to name the procedures by what they do, like the procedure above, which gets Address information out of the database. The AS keyword lets SQL Server know that the rest of the information we’re passing it will be the code of the stored procedure. After that, I put in the query to retrieve information from the Management.Address table.
It’s really that easy to create a stored procedure.
Editing a procedure
It’s generally not considered a good practice in stored procedures to use SELECT * to automatically return all columns. In keeping with that practice, we should modify our procedure to use a column list instead of the * wildcard. Here’s how you do that:
ALTER PROCEDURE Management.GetAddressInfo
AS
SELECT a.AddressID,
a.AddressLine1,
a.AddressLine2,
a.City,
a.Country,
a.Province
FROM Management.Address AS a;
There’s not a lot to explain here. The ALTER statement takes the place of the CREATE statement and other than that, the code is exactly as if you were creating the procedure for the first time. Unlike the ALTER TABLE statement, there’s no way to modify a piece of a procedure. To change the underlying code that defines the procedure, you have to change the whole thing.
Executing a procedure
Now that you have a procedure in your database, it’s time to make it work for you. From this point forward, if you want to retrieve that particular set of columns from that particular table, you no longer have to type out a SELECT statement. You can simply execute the procedure that you have.
EXECUTE Management.GetAddressInfo;
Since this stored procedure doesn’t have any parameters, that’s all that’s necessary to get it to run. If I run it on my system the results look something like figure 1.
Figure 1 The syntax to execute the GetAddressInfo procedure and the results of executing it.
The EXECUTE key word can be shortened to EXEC, just to save your fingers a little bit.
Dropping a procedure
Since change is pretty constant and because mistakes occur, it’s good to know how to remove stored procedures from the system as well as adding and modifying them. Getting rid of a procedure is a snap:
DROP Management.GetAddressInfo;
Because stored procedures don’t have dependencies like other objects, generally they will just drop immediately, so don’t make a mistake in typing or you could lose valuable code from your database.
Summary
Stored procedures are not a simple subject; don’t be fooled by anyone who says otherwise. But, if you break them down into their component parts, each of the individual parts is pretty simple to understand and use. We discussed why stored procedures are so valuable to you and your applications and how to create, edit, and delete procedures.