7

Learn about Stored Procedures

What is a stored procedure

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 procedure are commonly called 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

Stored procedures can be thought of having three main parts:

Inputs

Store 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 values 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.

Stored Procedure Parts

Though this example returns a single row, due to the fact 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 reason 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 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 you database.

Stronger security

You can set up you 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 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 integerate 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.

Kris Wenzel
 

Kris Wenzel has been working with databases over the past 28 years as a developer, analyst, and DBA. He has a BSE in Computer Engineering from the University of Michigan and a MBA from the University of Notre Dame. Kris has written hundreds of blog articles and many online courses. He loves helping others learn SQL.

  • Venkataraman says:

    Nicely written and balanced post! Thanks Kris!
    May I add a express of thoughts?
    Reusability of encapsulated logic is one big advantage. I also feel, SPs are a) independently assignable (so, specific skills in team can be built and exploited), b) independently testable (thus, promoting parallelism in development) and above all, pure fun to learn and write.

    A lot, however, depends on how SPs are designed. A good SP does only one simple task and returns one error message. It may return any number of result sets, though. Overloading functionality spoils reusability and maintainability. The reason, why I am harping on this is SPs have advantages (like any other tool), only when designed well.

    • Venkataraman says:

      Sorry for the typo in the second line: It should read as “May I express a couple of of thoughts?”

    • Hi Venkataraman,
      Thanks for posting! I totally agree with your points. I get frustrated when someone takes a either/or approach.

      Most tools exist for a reason and they all have their place in the technology stack.

  • John Walker says:

    Take a look at this link to know the benefits of using stored procedure in sql server: http://sqltechtips.blogspot.com/2015/11/stored-procedure-benifits.html

  • rashmi shivhare says:

    nice

  • magneto says:

    Hi,
    I have a stored proc which has two cursor inside it ,one is for fetching company details and other is for fetching the users for that company and my requirement is as follows.

    When i execute that stored proc I need to process the users in a Batch wise manner(say 1000 per batch). For Example if we have 10000 users for a company,if we execute the stored proc for the first time,TOP 1000 users should get executed which we can achieve using TOP 1000 in select statement inside user cursor,

    But for the second time if i execute the stored proc then we should be able to execute the next set (i.e., 1001 to 2000) in this case it should not process the records from 1 to 1000.

    Same way if i run the stored proc for the third time then it should process from 2001 to 3000 and so on..

    I am using MSSQL 2008 and how i can i achieve the above scenario? Please let me know if you have any feasible solution.Thanks.

  • >