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. You can get started using these free tools using my guide .
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 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 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 to 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 element, the query is color coded green, and the parameter red.
When the above commands are run from the query window the stored procedure prepared and stored in the database 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 45Here 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 store procedure we executed is equivalent to running this query
SELECT CountryName,
LatestRecordedPopulation
FROM Application.Countries
WHERE CountryID = 45The 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 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 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 wok 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?
Overtime I’d so 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 maintain 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 make 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 the 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 store 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 programing.
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 PROCEURE 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 PROCEURE 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 formal 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 blue and output parameter in green.
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 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 this 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
ENDWhen 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 @CountryLet’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!
Store procedures are mostly called by client program, either on web servers or other computers. If you’re interested in seeing how this is done.
Results from OUTPUT Parameter
Another way to obtain results from a stored procedure using output parameters. We talked about how to do this when 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, lets 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 EXECTE command format is
EXECUTE stored-procedure-name parameter1, paramter2, paramter2
If any parameters are OUTPUT parameters are you expect a result to be passed back, the 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
No comments:
Post a Comment
It’s all about friendly conversation here at small review :) I’d love to be hear your thoughts!
Be sure to check back again because I do make every effort to reply to your comments here.