Learn about Stored Procedures

What is a Stored Procedure?

A stored procedure is a group of one or more database statements stored in the database’s data dictionary and called from either a remote program, another stored procedure, or the command line. Stored procedures are commonly called SPROCS, or SPs. 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

Stored procedures can be thought of having three main parts:

Inputs

Stored procedure can accept parameter values as inputs. Depending on how the parameters are defined, modified values can be passed back to the calling program.

Execution

Stored procedures can execute SQL statements, utilize conditional logic such as IF THEN or CASE statements and lopping constructs to perform tasks.

A stored procedure is able to call another stored procedure.

Stored procedure 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

A stored procedure can return a single value such as a number or text value or a result set (set of rows). Also, as mentioned, depending on how the inputs are defined, changed values to inputs can be propagated back to the calling procedure.

Example Stored Procedure

Here is an example of a stored procedure that takes a parameter, executes a query and return a result. Specifically, the stored procedure 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 that we are matching to the primary key, stored procedures can also be used to return multiple rows, or a single (scalar) value.

Learn more in this introductory article!

Stored procedures can be called from within SQL server. To call this stored procedure from the SQL server command line or from another stored procedure, 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.

Benefits of Stored Procedures

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

Network Efficiency

Stored procedures 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 it isn’t uncommon for a database to handle requests from a custom program, such as C#, and a built in program such as Excel. In order 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.

Maintainable

When complicated business rules and programming logic are centralized into stored procedures, 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.

Once saved and compiled all calling program benefit from the change. Again, this can help increase the quality of your database.

Stronger Security

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

Security access can also be delegated. In effect, the stored procedure code is executed with higher access credentials than the caller. This means you don’t have to give every user that need to call your stored procedures all the access. In SQL Server for instance, you can use the EXECUTE AS clause with creating a stored procedure to impersonate another user.

Using stored procedure also helps prevent script injection attacks. Any input parameters are treated as literal values and not executable code. This make it harder for attackers to try and trick your queries into performing unintended actions.

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

Stored procedures 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 stored procedures. It definitely has gotten better than in the early 2000s, 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 IDEs, 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.

My Personal Experience

I feel that stored procedures have their place. I’ve put together some extensive applications and have used stored procedures for all the CRUD operations. I think that using stored procedures to insert and modify 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 stored procedures! We 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 or tweet me. I’m here to help you.

The post Learn about Stored Procedures appeared first on Essential SQL.