To Proc or Not to Proc – Building SSRS Reports

Question

Post reply

To Proc or Not to Proc – Building SSRS Reports

Viewing 4 posts - 16 through 18 (of 18 total)

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

Here's Microsoft's take on it, as well.

https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/stored-procedures-database-engine?view=sql-server-ver16

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

I'll also add that a lot of very well intentioned folks rail against the use of stored procedures because they "not portable". What they mean by that is that a stored procedure written to work in SQL Server (T-SQL) isn't going to work for Oracle, Postgre SQL, MySQL, etc, ad infinitum.

But, in most cases and only if the most basic of SELECTs is written into the code, neither will embedded code. Create just one variable and you'll see what I mean. And, if you do anything slightly exotic, like using a simple DATEDIFF(), you're in deep Kimchi because SQL is not SQL and the implementation of DATEDIFF() is different on a whole lot of databases.

A lot of folks say that ORMs are the answer to that and for things like basic C.R.U.D. (Create, Read, Update, Delete), they're absolutely correct but not so much when multiple tables need to be joined or anything other than super simple SELECTs are involved.. and ORMs sometimes even screw that up in a royal fashion.

And, please, no hate mail on that. I think ORMs are a HUGE time saver but look back at the MS link I posted and also understand that ORMs sometime lose the mind even when it comes to joins.

You also need developers that truly understand what "parameterization" is or you open yourself to a world of SQL Injection possibilities.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Ed B Hall of Fame Points: 3130 More actions · Answer 3

I have always used procs, maybe because I started with SSRS 2000 and it was terrible, so I did everything I possibly could in the database and just used SSRS to format and deliver the output.

I used to work with someone and I couldn't understand why their reports were so slow. The reason was that they were using inline sql as the source and then using a report parameter to filter the results. This meant that they were returning many times as much data as necessary and then filtering it locally.

I have not worked with reports for a while, so I don't know, but I assume you can use parameterized sql that filters at the server. However, with a stored procedure I can't see it working any other way, unless you went out of your way to write the proc with no parameters. Clearly there should have been some kind of release control to prevent such inefficient reports going to production, but there wasn't, and often there isn't, and report sql is invisible.

chris.johnson.0120 SSC-Addicted Points: 429 More actions · Answer 4

One benefit you get from using procs is you can restrict what the service account can see at a more granular level.

You can give the service account exec permissions on all procs in a schema, and then give the procs read permissions on the tables they need. That way you restrict the service account to only seeing the columns and rows the procs return.

Whether this is a level of security you care about depends on your company, but it feels more secure than giving the service account blanket read permissions across the database.

This reply was modified 2 years, 3 months ago by chris.johnson.0120.