Stored Procedures or SQL query which one is better for SSRS report Creation

Question

Hi Team,

which one is better to SSRS report Creation as a source query , Stored Procedures or SQL query and why ?

Answer 1

Hi @Bala Narasimha Challa ，
Stored procedures (SPs) are a better choice than views, but views are much better than SQL queries embedded in reports.

Here quote the reply from the external link:
When you embed a SQL query in a report (or an application or anything outside of the database) you are assuming that all of the objects referenced are not going to change in any way. This is firstly a big assumption (and assumptions are bad) and secondly a crippling restriction on the database owner - they can't change anything because it might break something somewhere.

When you use an SP or a view to access a database you make the reasonable assumption that the name of the object you are calling (the SP or view) won't change and that any parameter set will remain constant or at least stay compatible. Both approaches hide away the logic of the query from the caller - the logic can be corrected and improved over time without affecting the caller. The entire database can be refactored or even redeisigned as long as the name of the exposed object (and any parameters) remain the same and the caller will never know.

The advantage of using an SP over a view is that you can do far more. For example it's a good idea to validate that parameter values are within expected ranges. If you have a particularly complex query you can break it down into smaller steps, using temp tables for example. Moving on to very heavy queries you could even do interim maintenance steps in an SP, updating stats for example.

Here are the advantages and disadvantages of using Stored Procedures, Embedded Queries, and Views in the SSRS Dataset. For more information, please refer to: Pros and Cons: Stored Procedures–vs- Embedded Queries–vs- Views in an SSRS Dataset.
Hope this helps.
Best Regards,
Joy

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

If your query takes a long time to run I'd use a SP; if it's relatively simple then just use an SQL query.

Answer 3

I have actually found the Stored Procs to cause several issues.

1. tracking down the code to generate the data. Sometimes you end up with
   multiple stored proc calls.. Messy and time consuming when you have to change
   things to improve or change the data being pulled
2. Regular calls to stored procs actually slowing down things due to regular calls
   rather than just running the query and returning the data. Removing the stored procs speeded up things considerably in several cases
3. Stored Procs for simple queries.. just a waste of time with nothing to show for it
   and then your stuck back at #1. Most queries are simple if the database design is good.