Stored Procedures: the ultimate guide.

What is a Stored Procedure?

A stored procedure is a group of one or more database statements housed in the database’s data dictionary and called from either a remote program, another stored procedure, or the command line.  We commonly call them SPROCS, or SP’s.

Stored procedure features and command syntax are specific to the database engine.  Traditionally Oracle uses PL/SQL as its language; whereas, SQL Server uses T/SQL.

Main Parts of a Stored Procedure

They have three main parts:

Inputs

Store procedure can accept parameter values as inputs.  Depending on how you define the parameters, the proc passes modified values back to the calling program.

Execution

SPROCS execute SQL statements, and use control of flow statements, such as IF THEN or CASE statements and lopping constructs to perform tasks.

A stored procedure is able to call another.

SPROCS can become very handy as they can manipulate results of SQL queries via cursors.  Cursors allow the procedure to access results row by row.  In essence you can use cursors to loop through a SQL statement’s result.  This can slow down database performance, so be intelligent about your use of cursors!

Outputs

They can return a single value such as a number or text value or a result set (set of rows).  Also, as mentioned above, if configured for OUTPUT, parameters are able to propagate changed values back to the calling procedure.

Example Stored Procedure

Here is an example of a stored procedure that takes a parameter, executes a query, and returns a result.  Specifically, it accepts the BusinessEntityID as a parameter and uses this to match the primary key of the HumanResources.Employee table to return the requested employee.

Though this example returns a single row, due to the fact we are matching to the primary key, keep in mind stored procedures can also return multiple rows, or a single (scalar) value.

It possible to execute a stored procedure from within SQL Server.  To call it from the SQL server command line or from another one you would use the following:

exec HumanResources.uspFindEmployee 3

Stored procedures can also be called from within a programming language.  Each language, such as PHP or C#, has its specific methods for doing so.

Creating Stored Procedures

Now that we have seen an example, let’s look to see what it takes to create one for ourselves.

When writing stored procedures, remember most SP’s purpose is to run queries! Given this, SQL such as SELECT statements, make up a majority stored procedure’s code.

You’ll see their code or programming is categorized as:

Definition Statements

These are the statement you us to create the SPROC and it’s parameters.

You’ll use parameters “to pass” values from a call procedure to the current. Reference the parameters directly within your code, or assign them to T-SQL variables.

Control of Flow Statements

Allow you to use logic and looping constructs within your program.

Use IF/THEN blocks to control which portions of your program are executed.

To reduce repeating statements consider using WHILE loops.

SQL

Commands issued against the database to query, modify, or define data. Stored procedures truly shine, when start to combine control of flow concepts, such as While loops, with Dynamic SQL.

The idea here is to create and execute SQL at runtime. Using this approach you’re able to construct a SQL statement on-the-fly, store the statement in a variable, then execute it.

Try it yourself! Check out this stored procedure video walk-through.

Stored Procedure Benefits

There are several benefits to using stored procedure.  Though it doesn’t make sense to encapsulate every query you write, there are some good reasons to use them.   Here are some benefits often mentioned.

Network Efficiency

They can include many commands and process large amounts of information to obtain the desired result.  By keeping all the programming logic on the server, we can avoid having to pull query results across the network to be processed by a client program.

Encapsulate Business Logic

Databases can handle many clients and calling programs. But isn’t uncommon for a database to handle requests from a custom program, such as C#, and a built-in program such as Excel.

To ensure key business logic remains consistent, it makes sense to run the business logic on the server in stored procedures rather than rely on each version of a program to implement and properly execute the rules.

This helps with consistency, as programs execute the same logic.  This also means that the quality of the data is better.

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 keep 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 supplying a stored procedure, we can place this business logic within the SPROC, have it housed in a central location, the database, and callable by each programmer’s program.

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

Maintainable

When you organize complicated business rules and programming logic into SPROCS it makes making changes much easier.  Rather than having to hunt down areas in each application and make changes, you only need to make changes to the stored procedure.

They make it easier to support complicated code as they promote modular programming.

Keep in mind that they can call each other.  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 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 support.  It is a tradeoff.

Testing Stored Procedures

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

Stronger security

You can set up your database security so that applications can only access and change data through stored procedure calls.  Ad-hoc queries or direct access to tables would not be allowed.

Since stored procedures are located 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 it.

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

The SP treats input parameters as literal values and not executable code.  This makes it harder for attackers to try and trick your queries into performing unintended actions.

Speed / Optimization

They 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 the DBMS caches them, so once executed, the queries, are ready for repeated execution.

Dig Deeper:  Check out these performance tips you can use today.

Stored Procedure Disadvantages

There are some disadvantages to using stored procedures and you’ll find some blog posts encouraging you to not use them.  Some of the disadvantages I think are worth mentioning include:

Portability

They are written in vendor specific languages and that makes it hard to transfer them from one installation, such as Oracle, to another like SQL Server.

Testing

Testing and debugging stored procedures can be tricky.  It can be more difficult to put together the debugging tools to allow you to step through and trace them.  It definitely has gotten better than in the early 2000’s, but still not as easy as it is to debug native code.

Version Control

It is harder to keep track of changes made to stored procedures than it is native source code.  Unlike most native code IDE’s, there aren’t many ways to integrate source code control into the stored procedure development tool.  Because of this, most version control activities are limited to extracting the stored procedure as a CREATE PROCEDURE script and then manually importing into a version control system.

Update: I’ve found that using GIT, combined with Azure Data Studio, is a great way to manage stored procedure code.

My Personal Experience with SPROCS

I feel that stored procedures have their place.  I’ve put together some extensive applications and have used them for all the CRUD operations.  I think that using stored procedures to insert and change data is a really good idea.

I’ve seen that the systems I wrote that extensively used stored procedure passed security tests and were less prone to raise vulnerability testing issues.

However, even in these applications I found that you can’t solve all your problems using SPROCS!

I had put together some extensive ad-hoc report writers and needed a way to generate SQL code on the client and present it to the server.  For these ad-hoc cases I shy away from stored procedures.

Remember!  I want to remind you all that if you have other questions, you want answered, then post a comment.  I’m here to help you.