SSRS & Stored Procedures

[James Beresford]

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.



Get More from the original blog...