Dataset command type: Text vs. stored procedure

Question

Dataset command type: Text vs. stored procedure

Walter Levy

Mr or Mrs. 500

Points: 540
More actions
June 4, 2010 at 5:50 am

#236074

My supervisor and I have a deep disagreement about best practices regarding choice of command type for SSRS datasets. I want to use stored procedures for all of my datasets, whereas he thinks most datasets should use the Text command type (T-SQL code embedded in the RDL).
My argument is that using stored procs for everything promotes consistency and code reuse, and makes it easier to maintain reports.
His argument is that many stored procs are cluttering up the catalog... it can take a while for the Stored Procedure node to expand in SSMS. He feels that they are an extra layer of abstraction that must be navigated when tracing the logic of a report.
I'm looking for some arguments on either side of this question. Hoping to find more support for my point of view, but I'm open-minded about this. Maybe I'm wrong.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

POCTOB Right there with Babe Points: 790 More actions · Answer 1

You can change business logic in Sp without modifying and re-deploying report and as long as number of fields and their names are the same.

Steven.Howes SSCertifiable Points: 6230 More actions · Answer 2

I'm in the process changing all my reports to use SPs. I'm lucky, we report off of replicated DBs so the only things in my catalog are the report SPs. I like being able easily find and examine a scripts, not having to go through the clickfest to look at it a report. I gotta say (I didn't do a good enough job at this) A GOOD NAMING CONVENTION IS KEY! I also like being able to hide the tables names from users, 99% of our users don't want to know, but all it takes is one... And one more word SQL Injection...

There are some cons, I've modified scripts that I thought were single use only to find that I broke another report. I also find that sometimes the report builder doesn't like to refresh the fields without some kicks to the backside. I can see your bosses point about clutter, it's hard sometimes to find one script in a list of a a thousand. Filters in SSMS are good for this. If he's worried about clutter maybe throw in some schame serparatoin for good measure?

checkai Hall of Fame Points: 3041 More actions · Answer 3

We utilize all SPs in our reports, even for basic parameters. The value we see:

1. Consistent naming to know which procs are being used.

2. No re-deploy or report touch when a data only mod is needed.

3. Power to create some logic intensive reports

Walter, I don't consider how the "Stored Procedures" folder expands within management studio as an argument... 😉

Pvm_25 Ten Centuries Points: 1073 More actions · Answer 4

Sometimes I am seeing performance difference while using SPs over Text. (Report takes longer time to execute). So most of the time I try both the approach and use Profiler and Exectionlog to compare the data retrieval time. And if the dataset is being used in multiple reports then I use SP only so if business rule cahnges I don't have to go through all the reports to modify that.

MaricopaJoe Ten Centuries Points: 1190 More actions · Answer 5

I have had the same disucssion with several folks and can see both sides of the fence. I think what blows the sp concenpt into the water is the 'shared dataset' that is now avaiable in ssrs 2008 r2. Seeing the sp conversion issues that people went through from Sql 2000 to Sql 2005, I prefer to use the report logic as much as possible and avoid the sp.

Shared datasets in 2008 put the use of sp's as no longer needed.

Alasdair Thomson Hall of Fame Points: 3630 More actions · Answer 6

I started using SSRS with text datasets.... The reports were great, spent ages on them... Unfortunately our database had a major breach with loss of some important data. After checking and investigating and advise from sites like this found that we had a user perform an SQL Injection attack... I will never, ever, ever use anything but SQL SP's!

Walter Levy Mr or Mrs. 500 Points: 540 More actions · Answer 7

Thank you all for your feedback!

I hadn't heard about shared datasets, will look into that.

I suspect that we'll use Text datasets for simple selects (e.g., dropdown lists), and SPs for "meatier" processes/queries.

P Jones SSChampion Points: 12361 More actions · Answer 8

I prefix my stored procs appropriately - the reporting ones are rp_ , data access layer ones are dap_ and lp_ for drop down list ones which can be used in both the application AND the reports - good argument in favour of stored procs.

Don't use sp as a prefix though.

POCTOB Right there with Babe Points: 790 More actions · Answer 9

Good, prefix sp is used for system stored procedures

Walter Levy Mr or Mrs. 500 Points: 540 More actions · Answer 10

I like your prefix idea but would use different ones. How about rpt_ for reporting, da_ for data access, and dd_ or lst_ for dropdown?

MaricopaJoe Ten Centuries Points: 1190 More actions · Answer 11

This debate will never end. Should you use a case statement in the sql vs. iif in the report expression. It's all semantics.

Here is more on shared datasets.

http://prologika.com/CS/blogs/blog/archive/2009/11/16/shared-datasets.aspx

:rolleyes:

ezytime SSCommitted Points: 1846 More actions · Answer 12

What's the easiest way to pass the results of a multi-select parameter to a SP? If this was as straightforward as it is with an embedded text query, I'd use a lot more SP's in reports.

Walter Levy Mr or Mrs. 500 Points: 540 More actions · Answer 13

Re passing multi-select parameters:

My approach is to use a table-type UDF to put the multi-select parameters in a table variable, then typically do an inner join on that to constrain the data.

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 14

I like the use of SP's as others have mentioned because of the ease of editing the sp without going to the report definition, and to avoid SQL injection the lazy way(although it's perfectly possible to write good SQL injection proof code without it)

couple other comments:

sp is not the prefix used for the MS stored procedures (which we want to avoid), sp_ is (sp followed by an underscore). You can name your procedures spCheeseWhiz and be just fine.

Thanks for the link on Shared Datasets MaricopaJoe, but I'm not seeing (at least from that link) how these would "blow the sp concept into the water", or make them no longer needed? It would be an alternative if your sp took a long time to pull data, but it looks to solve problems of latency more than anything else. If that's not a concern, what would be the advantage of a Shared Dataset over an sp?

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."