Dataset with Text or Stored Procedure

As most of you already know, Reporting Services reports make use of Datasets to get data. These Datasets can get the data from Text, Table (rarely used) or a Stored Procedure. Which query type to use has been a topic on many forum boards and I’ve had this question pop up several times over the years during presentations and general discussion. Sometimes asked “Which one is better to use; a stored procedure or text?” or “What is considered best practice…?” With that being said, I think it is long overdue that I write a blog about it to have as a reference to provide to others (not “the others” that were on LOST).

When these questions are brought up to me, I start with the general “it depends” answer. It depends on what perspective you are looking at when asking which is better. Best practice would say that you should put your queries as close to the data as possible (especially calculations). When you create a stored procedure, it gets compiled and its execution plan is available for re-use (most of the time). This can increase performance and make a commonly used query easier to maintain across many reports. For example, let’s say that you have 20 reports across your organization that uses a parameter for State. Each report could have a Dataset containing a Text query type that returns all of the StateID and StateName columns. When the dataset needs to be changed, you would need to change it in all 20 reports. However, if you use a stored procedure, you would only need to update that one stored procedure and you’re done! So from this perspective, I would (and do) use a stored procedure.

However, from the maintenance perspective, if you are making many updates to your reports and underlying stored procedures, it makes it more difficult to maintain the separate objects. When you deploy a report containing one dataset using a stored procedure, two objects need to be deployed independently of one another (report definition to RS Server and stored procedure to db containing the data). In the “real” world, many reports contain more than one dataset. As such, when moving reports from one environment to another you’ll, want to script out the database objects that need to be deployed and deploy it along with the report. This makes the deployment a little more manageable. It can be a nuisance, but more often than not, it outweighs having inline text at the report level.

So, with all that, the only real good thing about using the query type of Text is that maintaining the report becomes easier. Also, creating reports during demonstrations is easier when you copy and paste a query into the Text box too or using the Query Designer to build a query out. However, you can use the graphic designer in SSMS as well. J

I hope that you have enjoyed this blog and if you don’t already, please be sure to follow me on twitter at @briankmcdonald. Also note that you can subscribe to an RSS feed of my blogs or find me on LinkedIn, SQLBIGeek, BIDN Blogs, BIDN Articles, SQLServerCentral or SQLServerPedia.

Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant