SSRS & Stored Procedures

As an ETL Monkey, my experience with reports has been a bit incidental. One thing that puzzled me though is why report developers always wrote stored procedures to generate data for their reports, instead of using the SQL capabilities within the report. They said “Best Practice” and I was happy to leave them to it!

In this detailed post Adam Haines explains why. It’s very detailed so here are the key points if you have a short attention span:

  • The query can be maintained independently of SSRS, allowing tuning the query without accessing or modifying the reports
  • The execution plans can be cached if you use an Stored Procedure, but not if you use SSRS
  • Stored procedures allow the use of certain objects that cannot be used in embedded T-SQL in the report such as temp tables and indexes specific to those temp tables as well as table variables
  • Stored Procedures provide a layer of abstraction between the report and the business logic
  • Stored Procedures allow re-use of similar logic

Credit for the above list to my colleague John Simon who authored most of the above points in an internal discussion.

One thought on “SSRS & Stored Procedures

  1. Hi James,

    I agree on most of the benefits outlined, although the comment about execution plans isn’t 100% correct. SQL Server 2005 and later editions have had the ability to cache commonly used query plans, even with different parameter signatures. However, you can do more to create a static execution plan with a stored procedure than you can with raw T-SQL, and report-based T-SQL may also be prone to SQL Injection.

    On the downside, there’s a piece of management overhead with stored procs that needs some governance wrapped around it:
    * Parameter and field names/types must be consistent regardless of underlying changes to the stored proc definition.
    * Fields can be added safely, but parameters cannot unless they are optional and have a default value.
    * Fields used in a report should not be removed from the underlying query/proc.

    Most of this sounds like common sense, but it’s amazing how many people forget this stuff in production environments.

    Cheers

    Jeremy Huppatz
    http://ozziemedessql.blogspot.com

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>