THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Rob Farley

- Owner/Principal with LobsterPot Solutions (a MS Gold Partner consulting firm), Microsoft Certified Master, Microsoft MVP (SQL Server), APS/PDW trainer and leader of the SQL User Group in Adelaide, Australia. Rob is a former director of PASS, and provides consulting and training courses around the world in SQL Server and BI topics.

Stored Procedures with SSRS? Hmm… not so much

Hi! - Great that you've found this page, but it's no longer here! You can find the content over at: http://blogs.lobsterpot.com.au/2011/11/22/stored-procedures-with-ssrs-hmm-not-so-much/

Published Tuesday, November 22, 2011 2:57 PM by Rob Farley

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Rob Farley said:

It's nice to see a discussion over at http://sqlblog.com/blogs/merrill_aldrich/archive/2011/11/07/t-sql-tuesday-24-ode-to-composable-code.aspx, where Jamie & Merrill discuss the value of TVFs too.

November 21, 2011 11:23 PM
 

Boyan Penev said:

Good one. I'll try it next time.

Typically, I wouldn't return errors in the same sproc which returns the data set, but it is a fair point. What gets me thinking is how we can stop building stuff like usp_rs_<reportname> for each report and substitute with something more reusable. Especially considering that in a typical data mart we end up writing similar queries for similar reports.

Would've loved to see a little argument mentioning how bad it is to write _all_ of the code in SSRS, though...I still have to convince all sorts of devs/dbas that writing all the code in SSRS is rubbish too...

November 22, 2011 3:48 AM
 

Rob Farley said:

Hi Boyan,

Yes, I wouldn't be recommending joining functions to other functions.

Using TVFs instead of procedures can be really neat. Extra columns can be added without breaking reports, and using the principles shown in http://bit.ly/SimpleRob, you can make joins redundant and far better performance than less-than-ideal procedures.

Rob

November 22, 2011 4:54 AM
 

Andrew Tobin said:

I do have some good news though, they're considering Multiple sets returned for 2008.

Oh.

http://connect.microsoft.com/SQLServer/feedback/details/308493/allow-multiple-datasets-in-one-reporting-service-sql-query

(A connect request I put in in 2007 asking for it to be considered ;))

November 24, 2011 8:26 PM
 

Greg Low said:

Hi Rob,

What I like to see is that a database object is called from RS. I don't care what type of object it is. I prefer sprocs though. My main interest is that I don't want embedded T-SQL in reports. Every time code gets embedded somewhere outside the database, another minefield is left for anyone that ever needs to refactor or work on the database, as they have no visibility on the code being executed.

I do often get frustrated with some of the limitations of the SQL Server datasources in RS and I often wish I could find the query option that says "just run with me on this" when it wants to have a parsing whinge. Most of those issues I can get around by using an OLEDB datasource instead of a SQL Server one.

November 24, 2011 11:54 PM
 

Dragan Babovic said:

The valid point has been made at the end of the article. Inline UDF should be the first choice whenever possible including reports. But saying always use UDF is wrong as well. There is no tool that is the best choice in every situation. So "Everything in..." is really a bad way to begin the sentence. Stored procedures have their place but should not be used always, nor should UDFs. I've seen many complex reports where sprocs were the only choice.

It is a shame that the author didn't use raiserror rather than the select statement in his example for handling  error conditions. After all raiserror is meant to be used for well, raising errors.  The point would be equally valid.

November 26, 2011 12:16 PM
 

Alexander Kuznetsov said:

You are suggesting a reasonable workaround, of course, but would it not be better to fix the root cause? FMT_ONLY should be deprecated, as it leads to issues like the one you described. Instead of using it, SSRS should allow us to execute whatever we want and let us specify the parameters we want to call it with.

I ran into a similar issue when I was dragging a stored procedure onto Linq pad. The procedure was storing intermediate results in a temp table, and FMT_ONLY choked on it. So I wrote my own little tool to replace the one that comes with VS 2010 - it just works, and it took me like half an hour to develop. Instead of using the unreliable FMT_ONLY, I just use a string to get the parameters to invoke the module with - it is that easy to get it right.

November 27, 2011 9:51 PM
 

jonmcrawford said:

I would disagree with you Rob, only because I don't think that a function should be used (conceptually) to return a resultset, it should be mapping input to output. While that *may* be the case, it also might not be for these reporting situations, where you might have no input at all, you might have input of a date range and return a huge dataset, etc. I do see your point about the multiple results returned from FMTONLY, and the validation in the WHERE is a definite improvement.

November 28, 2011 10:30 AM
 

Chuck Desmarais said:

FMT_ONLY is the problem, and SSRS should either get rid of it or make it optional.  However one workaround that I find prettier is to put a dummy select at the beginning of the proc.  IF 1=2 SELECT Null as [Column1], Null as [Column2]...etc.  It will never get called but trick FMT_ONLY into thinking it knows the return values.

November 28, 2011 2:21 PM
 

Piers7 said:

In similar vein, SSIS likes to use SET Rowcount 1 to get its metadata... or poison the plan cache, depending on how it feels. http://piers7.blogspot.com.au/2009/06/nasty-ssis-2008-issue-with-table-or.html.

April 27, 2012 9:11 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.

News

News? Haven't you read my blog?

My Company


Can't find something?

Contact Me

IM: rob_farley@hotmail.com
Twitter: @rob_farley
Skype: rob_farley
E: rob_farley@hotmail.com

MVP (SQL Server)




Certifications








Adelaide SQL UG

Privacy Statement