Hi Everyone,
Stored Procedures vs Embedded SQL
Stored Procedures as a Dataset Source for an SSRS Report
Pros
- Performance: the ability for the stored procedure to reuse a query plan (stored procedure cache)
- DBA can tune more effectively, if needed
- Permits the DBMS to secure the object, if needed
- Centralization of queries into views is often preferable to DBAs because the queries are more transparent to them
- Provides a layer of abstraction between the database tables & the report (for example: you can alias column names, create derived fields, minimize the effect of other physical object changes, or show less fields to simplify the reporting process)
- Provides an additional layer of security since "Execute" permissions are required for either the user running the report, or an impersonation account
- Ability to query system tables to find usage of tables & columns (which may help with change management)
- For a minor change, permits the ability to alter the stored procedure without requiring the RDL to be redeployed
- Refactoring the database is easier
- Unit testing of the code is much easier. You can easily build tests to just call the stored procedures. While possible via the web service interface, it's much harder to test the reports directly and requires a different skill set
- It allows the UI to be built by one person and the stored procedures to be built by another
- Benefits on procedure cache efficiency
- The same stored procedure may be used on multiple reports.
Cons
- Harder to manage security
- Need “Create” permissions on the source database or need another person to create the stored procedure for you
- Slightly more knowledge is required to create a stored procedure than a simple select statement
- Can clutter up the database with quite a few simple queries and/or redundant queries
- Additional handling is needed to parse multi-valued parameters in SSRS
- Two-step deployment (the stored procedure, and/or the RDL); this creates an opportunity for error if not deployed concurrently
- Additional testing of a changed stored procedure & the effect of the change on the report (which may take slightly more time since they are separate)
- May require additional personnel / time / coordination of efforts if the stored procedures are maintained & enhanced by staff other than the reports (for example, if a field changes, or a new parameter is requested)
- Report may break when schema changes
- Difficult to make changes to embedded TSQL
- Causes procedure cache to bloat
Embedded SQL in SSRS Dataset
Pros
- Easy (less syntax for a beginner to learn)
- No “Create” permissions needed on the source database
- One-step deployment (unless you are using a Shared Dataset, which is stored outside of the RDL)
Cons
- For large datasets, may not perform as well as a stored procedure
- Greater possibility that individual report queries are redundant or handling logic in different ways (a great way to combat this is to use Shared Datasets, a new feature in SQL Server 2008 R2)
- SSRS Query Designer doesn’t retain formatting well
- SSRS Query Designer removes comments (a big shortcoming in my opinion)
- SSRS Query Designer renames aliases in some circumstances
- The report developer may need additional permissions to database objects to construct the queries (i.e., if direct table access is being utilized instead of views)
- Need to open the report in BIDS (or Report Builder) in order to make a change
- Much more difficult to monitor the database objects being accessed by these queries (i.e., not as easy as querying the system tables with a stored procedure)
Please note that this is not the be all and end all to this design decision but it should provide one with valid reasons why they would choose one way over the other. I do however advise a combination of the 2, that is, were 1ne wants to populate the report's parameters, for example, they would rather use embedded T-SQL instead of a Stored Procedure and were 1ne wants to populate the report's main dataset, they would a use Stored Procedure over embedded T-SQL.
That's it for this session but before we go, here's a list of the references for this article. There are some clever people that write about the wonders of SQL and I've gained a lot from reading their blogs. So go ahead and have a look at what they do.
References:
