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:
- 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
. - 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:
DECLARE @Country TABLE (
CountryName NVARCHAR(60),
LatestRecordedPopulation BIGINT
)
INSERT @Country (CountryName, LatestRecordedPopulation)
EXECUTE Application.uspFindCountry 45
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.
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:
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: