which one is better to SSRS report Creation as a source query , Stored Procedures or SQL query and why ?
SQL Server Reporting ServicesA SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,911 questions Sign in to follow 0 comments No comments Report a concern I have the same question I have the same question 0
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.
