Introduction to Stored Procedures for SQLServer

After reading this article, you will understand the benefits of stored procedures and how to create and execute them.

All the examples for this lesson are based on Microsoft SQL Server Management Studio and the sample databases AdventureWorks and WideWorldImporters.

What is a Stored Procedure?

A stored procedure is a way for you to store a set of SQL statements and accompanying programming statements within the database and run them later.

Stored procedures come in handy as they allow you to combine both procedural logic as well as SQL statements. This makes SQLServer very flexible, as SQL by itself isn’t suitable to tackle all problems, and being able to call upon procedural logic to string together multiple SQL statements into one step is handy.

For those familiar with the concept of a programming, a procedure is a set of instructions used to perform an action. In databases, this is taken a step further; database stored procedures, are more specialized and built to perform specific database tasks such as inserting or deleting data.

Quick Example

Before we get too far along, let’s look at a simple stored procedure I created based on the WideWorldImporters sample database.

This stored procedure executes a query and returns the result as a result set. What makes it special is the query accepts a parameter @CountryID.

CREATE PROCEDURE Application.uspFindCountry
                 @CountryID [int]
AS
BEGIN
   SET NOCOUNT ON;
   SELECT CountryName,
          LatestRecordedPopulation
   FROM   Application.Countries
   WHERE  CountryID = @CountryID
END

To help you see the various elements, the query is in italics, and the parameter is in bold.

When the above commands are run from the query window, the stored procedure prepared and stored in the database is ready to execute. Of course, EXECUTE is used to run the stored procedure.

Here is an example showing how to run our example with a parameter:

EXECUTE Application.uspFindCountry 45

Here is the result:

In our example, the result is a row set, but it doesn’t have to be. We can return a scalar value as well.

Also, in addition to returning the result, in later articles, we will show you how to return values via OUTPUT parameters or the RETURN command.

In case you’re wondering, this stored procedure we executed is equivalent to running this query:

SELECT CountryName,
       LatestRecordedPopulation
FROM   Application.Countries
WHERE  CountryID = 45

The big advantage our stored procedure brings is that we haven’t hard-coded the country code. This makes our query more flexible.

I’m sure from looking at this example, you have seen other benefits to stored procedures. Let’s go over them in detail now.

Benefits of Stored Procedures

There are many advantages to using start procedures, top among them is security.

Security

Since stored procedures are stored within the database, you can set up security to restrict which users are able to see and execute them. This is handy if you have sensitive queries or code which you want to run in a controlled manner.

Not only can you control who can see the stored procedure code, and perhaps sensitive information contained in the queries themselves, but you can control access to who can execute them.

You can use the GRANT EXECUTE command to set specify who can run a stored procedure.

Also, by using stored procedures, we can avoid SQL injection attacks. These can be avoided by parameterizing your queries, which stored procedures lend themselves well.

Isolation of Business Rules

As you start to write more complicated applications, you’ll start to see that your business logic becomes scattered between various queries, and client programs. In some cases, you’ll rely on several programs to “behave” appropriately to maintain data integrity.

For example, in the AdventureWorks database, a person can have up to three phone numbers: cell, home, and work. Suppose, whenever a person is added, we need to also add two of these numbers; their home and work number. How can we easily ensure this?

We could rely on each developer to write code to create the insert statement to add both phone numbers? But if there are many programs and programmers, can we ever hope for them to be consistent?

Over time, I’d say no, but there is a solution!

By providing a stored procedure, we can place this business logic within the procedure, have it stored in a central location, the database, and callable by each programmer’s program.

This way, the logic is maintained centrally, easy to maintain, used by all, and consistent.

Maintainability

Stored procedures make it easier to maintain complicated code as they promote modular programming.

Keep in mind that stored procedures can call other stored procedures. This is important, it allows you to take a very complicated process, and break into down into manageable pieces. Ideally, each piece performs one job, and does it well.

Additionally, by pulling the business rules into the stored procedures, we centralize the code. Having this logic in one place makes it easier to troubleshoot any issues, and chase down inconsistencies.

Beware! If you break your problem into too many pieces, it can become too hard to maintain. It is a tradeoff.

Speed / Optimization

Stored procedures generally run faster than individual statements called from client programs.

They reduce network traffic since you’re sending one command to execute the stored procedure over the wire rather than several queries to execute.

Also, in addition, stored procedures are cached on the server, so once executed, the queries, are ready for repeated execution.

Testing

When you place business rules within stored procedures, you have an opportunity to test them independently of any applications you’re building.

Defining Stored Procedures

Let’s now talk about how you define and then run stored procedures. We’ll look at the CREATE PROCEDURE command, various ways to define parameters, how to return values from a stored procedure, and finally, how to run a stored procedure using the EXECUTE command.

We’ll use an example to help us along, but we’ll keep it simple, as I want to focus on the procedure’s definition more than its internal programming.

For our example, consider programming a stored procedure named uspCalcArea, which accepts two parameters, height, and width, and then outputs a third parameter area. If the procedure successfully completes, it returns 0; otherwise 1.

The area is calculated height multiplied by width.

For the example, we are going to calculate the area of a rectangle: area = width x height.

I’ll show you the final procedure first, then we’ll go through each part and command step by step.

CREATE PROCEDURE uspCalcArea
                 @height float,
                 @width float,
                 @area float OUTPUT
AS
BEGIN TRY
   SELECT @area = @height * @width;
   RETURN 0
END TRY
BEGIN CATCH
   RETURN 1
END CATCH;

CREATE PROCEDURE

To define our procedure, we use the CREATE PROCEDURE command. The general format for the command is CREATE PROCEDURE procedure-name. In our example, we’ll use:

CREATE PROCEDURE uspCalcArea

There are a couple of items to consider:

1. It’s best practice to preface your stored procedure with usp, which stands for User Store Procedure. This helps it stand out from system stored procs, which are prefaced with sp.
2. Once a stored procedure is created, then using CREATE PROCEDURE to modify a procedure throws an error. In this case, if you want to change one, then use the ALFTER PROCEDURE command, or first DROP the procedure prior to rerunning the CREATE PROCEDURE script.

Once you have declared the procedure name, you can declare and input or output parameters.

Input parameters are used to provide the procedure with values. In our example, these are the height and width.

Output parameters are used to obtain results from the stored procedure. Output parameters are denoted with the OUTPUT keyword. We’ll use an output parameter to pass the area back to the calling program.

When naming parameters, the first character for a parameter name must be an @. Parameter names must abide by rule for data base identifiers.

Now considering parameters, the general format for a stored procedure declaration is:

CREATE PROCEDURE procedure-name
                 @parameter1 dataType,
                 @paratmer2 dataType,
                 ... more
AS
   Procedure body begins here…

For our example, I’ve highlighted the input parameters in bold and output parameter in italics.

CREATE PROCEDURE uspCalcArea
                 @height float,
                 @width float,
                 @area float OUTPUT
AS
BEGIN TRY
   SELECT @area = @height * @width;
   RETURN 0
END TRY
BEGIN CATCH
   RETURN 1
END CATCH;

Notice that each parameter begins with an @, and that each parameter is separated by comma. All our parameters are defined as type float, which is one of the seven types you should know! The last parameter is our result so it defined as an OUTPUT parameter.

Returning Values from a Stored Procedure

There are several ways to return values from a stored procedure, through the result of a SELECT statement, using output parameters, or return codes.

Results from SELECT Statement

Let’s first talk about using the SELECT statement. We first saw how to do this in our very first stored procedure example:

CREATE PROCEDURE Application.uspFindCountry
                 @CountryID [int]
AS
BEGIN
   SET NOCOUNT ON;
   SELECT CountryName,
          LatestRecordedPopulation
   FROM   Application.Countries
   WHERE  CountryID = @CountryID
END

When this stored procedure is executed, the SELECT statement generates a result which is accessible by the calling program. If you use the EXECUTE command in SQL Management Studio, then the results are simply displayed.

Let’s see how to harness this result and insert it into another table. Try this example:

--Setup Temporary Table
DECLARE @Country TABLE (
           CountryName NVARCHAR(60),
           LatestRecordedPopulation BIGINT
         )

--Place results in Temp Table
INSERT @Country (CountryName, LatestRecordedPopulation)
EXECUTE Application.uspFindCountry 45

--Prove Results are There!
SELECT COUNT(1) as Proof FROM   @Country

Let’s see how this works.

This example first setups up a temporary table names @Country.

Then the stored procedure uspFindCountry is run. The results are added to @country using INSERT.

We see the results are there by counting the number of rows.

Here is the proof!

Stored procedures are mostly called by client program, either on web servers or other computers. If you’re interested in seeing how this is done, check out how you can use C# to execute stored procs.

Results from OUTPUT Parameter

Another way to obtain results from a stored procedure using output parameters. We talked about how to do this when we talked earlier about stored procedure parameters.

Output parameters allow the calling procedure, to pass a value into the procedure, allow it to be changed, and then the calling program or procedure to access the results.

A stored procedure can have zero, or one or more parameters. If parameters are defined, any of these can be output parameters. Output parameters are denoted using the OUTPUT keyword.

Let’s use our uspCalcArea as our example:

CREATE PROCEDURE uspCalcArea
                 @height float,
                 @width float,
                 @area float OUTPUT
AS
BEGIN TRY
   SELECT @area = @height * @width;
   RETURN 0
END TRY
BEGIN CATCH
   RETURN 1
END CATCH;

To show how this works, let's first assign the variable @areaParam the value 0. Then, we’ll call uspCalcArea, to get the area of a 5 x 3 rectangle. Once the procedure completes and result is calculated, the area of 15 is passed back to our variable.

We’ll display the variable as proof.

Results from RETURN Value

The RETURN command is used to stop the stored procedure’s execution and return immediately to the calling program. When this occurs, you’re given the opportunity to send a return code. This can be a useful way to let the calling program know whether the stored procedure executed successfully, or if there were errors.

The general format for the RETURN command is:

RETURN integer-value

If no value is specified, then a value of 0 is returned. Since 0 is the default value, it makes sense to use this to signify the procedure successfully completed and other integer value to indicate other states of errors.

In our example, a return code of 0 signifies success; whereas; 1, means there was an issue.

EXECUTE

To run or call a stored procedure, we use the EXECUTE command. You can also use the shortened version EXEC. In its simplest form, the EXECUTE command format is:

EXECUTE stored-procedure-name parameter1, paramter2, paramter2

If any parameters are OUTPUT parameters and you expect a result to be passed back, then add the OUTPUT keyword. Let’s look at an example using the uspCalcArea stored procedure.

Recall our stored procedure accepts three parameters, height, width, and area. The area is defined as an output parameter.

Here is the execute command we can use to run this stored procedure.

--Calculate Area of 30 x 55 rectangle
DECLARE @area float;
EXECUTE uspCalcArea 30, 55, @area OUTPUT

Upon completion, @area is 1650.

The EXECUTE command is also used to capture the return code. We can run the same stored procedure and capture the return value using this sequence:

--Calculate Area of 30 x 55 rectangle and report back area and return value.
DECLARE @area float;
DECLARE @returnValue int;
EXECUTE @returnValue = uspCalcArea 30, 55, @area OUTPUT
SELECT @area AS area, @returnValue AS ReturnValue

Here is the output of the final SELECT statement: