Do You Want to Learn SQL?

Take our free eight day course, where I'll teach you in simple to understand English all you need to get started learning SQL.

-
DAYS
-
HOURS
-
MINUTES
-
SECONDS

Free Beginners SQL Course!

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.

Example Stored Procedure

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.

8 responses to “Stored Procedures: the ultimate guide.”
  1. DominicVaja

    Hey Kris, I am beginner for SP.
    I have one SP with joins and in my db tables have stored 15 lacks row of each year, so my SP takes to much time to give result.
    SP like..

    DELIMITER $$

    CREATE PROCEDURE StoreProcTest()
    BEGIN
    SELECT ratecode AS CODE,
    ROUND(SUM(dr.total),2) AS totalRev,
    ROUND(SUM(dr.pDiscTotal),2) AS roomRev,
    ROUND(SUM(dr.pAdTotal),2) AS pAdRev,
    ROUND(SUM(dr.pVisibleAdTotal),2) AS aLaCarteAdRev,
    ROUND(SUM(rr.tax),2) AS taxRev,
    ROUND((SUM(rr.total)- SUM(rr.cancelledTotal)),2) AS materializedRev,
    ROUND(SUM(rr.cancelledTotal),2) AS cancelledRev,
    COUNT(DISTINCT dr.rReId) AS noOfReservations, COUNT(dr.rReId) AS roomNights,
    ROUND((SUM(dr.taxbasisTotal) – SUM(dr.pDiscTotal)),2) AS alternateAdjustment,
    SUM(dr.total) AS revenueContribution , rr.source , prop.defaultCurrencyCode AS defaultCurrencyCode
    FROM
    reportresdata rr
    LEFT JOIN reportresforday AS dr ON dr.rReId = rr.rReId
    LEFT JOIN
    (SELECT defaultcurrencycode, pname,pid FROM reportuserprop rp GROUP BY pid ORDER BY lastupdated DESC) AS prop
    ON
    prop.pid=rr.pid
    WHERE (rr.pid !=3 OR rr.pid !=4 OR rr.pid !=5 OR rr.pid !=7 OR rr.pid !=8
    OR rr.pid !=9 OR rr.pid !=38 OR rr.pid !=58 OR rr.pid !=65 OR rr.pid !=86
    OR rr.pid !=90 OR rr.pid !=93 OR rr.pid !=137 OR rr.pid !=280 OR rr.pid !=340
    OR rr.pid !=357 OR rr.pid !=394 OR rr.pid !=441 OR rr.pid !=570
    OR rr.pid !=571 OR rr.pid !=575 OR rr.pid !=645 OR rr.pid !=665
    OR rr.pid !=676 OR rr.pid !=826 OR rr.pid !=1014 OR rr.pid !=1015
    OR rr.pid !=1123 OR rr.pid !=1174 OR rr.pid !=1274 OR rr.pid !=1292
    OR rr.pid !=1368 OR rr.pid !=1423 OR rr.pid !=1428 OR rr.pid !=1433
    OR rr.pid !=1435 OR rr.pid !=1446 OR rr.pid !=1458 OR rr.pid !=1526
    OR rr.pid !=1529 OR rr.pid !=1530 OR rr.pid !=1546 OR rr.pid !=1547
    OR rr.pid !=1549 OR rr.pid !=1550 OR rr.pid !=1552 OR rr.pid !=1555
    OR rr.pid !=1556 OR rr.pid !=1557 OR rr.pid !=1558 OR rr.pid !=1560
    OR rr.pid !=1570 OR rr.pid !=1576 OR rr.pid !=1611 OR rr.pid !=1616
    OR rr.pid !=1617 OR rr.pid !=1624 OR rr.pid !=1627 OR rr.pid !=1629
    OR rr.pid !=1635)
    AND resstatus = ‘COMMITTED’ AND waschanged = 0
    AND (DATE(dr.date) >= DATE(‘2004-01-01’) AND DATE(dr.date) <= DATE('2019-12-30'))
    AND iscancelled = 0 AND noShow=0 GROUP BY rateCode, defaultcurrencycode;
    END$$

    DELIMITER ;

    Thanks in Advance..!!!

  2. magneto

    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.

    1. Hi,

      Can you pass in a parameter that is the batchrunnumber? Then you can use that to calculate record ranges.
      You may need to keep the batchrunnumber in a separate table to store it between runs.

      BTW – cursors are inefficient. Can you do the same processing steps using SELECT and MERGE?

      Here is a statement to get range of rows: https://www.essentialsql.com/using-offset-and-fetch-with-the-order-by-clause/
      Here is the merge statement: https://www.essentialsql.com/introduction-merge-statement/

  3. rashmi shivhare

    nice

  4. John Walker

    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

  5. Venkataraman

    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.

    1. Venkataraman

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

    2. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

More from the blog


MySQL PostgreSQL SQLite SQL Server