February 24, 2012 at 4:00 pm
My team mate suggested me its beter that SQL query be in the report rather than using SP. But i have parameters and how can i put sql in my report. he says there are more than 250 reports so when i write sp its not safe.
February 24, 2012 at 6:49 pm
raghuveer126 (2/24/2012)
My team mate suggested me its beter that SQL query be in the report rather than using SP. But i have parameters and how can i put sql in my report. he says there are more than 250 reports so when i write sp its not safe.
What does your team mate mean by "sp its not safe"?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 27, 2012 at 8:43 am
The other thing I would add is that having the sql server do the 'heavy lifting' inside the stored procedure is going to be faster than doing it inside the report.
February 27, 2012 at 10:00 am
Sounds to me like your "team mate" has either learned to do it one way and is afraid to learn how to do it the proper way or just refuses "change" altogether. What kind of reporting model are we talking about here? Crystal? Reporting Services?
There are MANY reasons why you should use a stored-procedure for reporting, here's two:
1. Once a report is built using a store-procedure, the report doesn't need to be re-compiled every time you tweak something in the procedure (unless you're going to change the columns returned in the final result set)
2. Stored-procedures are pre-compiled code (not ad-hoc SQL statements) and can take further advantage of a cached execution plan, generally resulting in faster performance (performance can depend on MANY things though), whereas ad-hoc SQL needs to be parsed by the query engine each and every time it's executed
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 27, 2012 at 11:51 am
The bit about procs being pre-compiled is actually the IT version of an urban legend.
But the bit about refactoring a proc being easier than rebuilding and redeploying a report is correct.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 27, 2012 at 12:34 pm
But fact that the execution plans for the procs are often cached and more likely to be reused isn't though. This isn't an option when running ad hoc select statements...is it???
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 27, 2012 at 1:04 pm
MyDoggieJessie (2/27/2012)
But fact that the execution plans for the procs are often cached and more likely to be reused isn't though. This isn't an option when running ad hoc select statements...is it???
It depends on the parameterization of the ad hoc query.
Take a look at this and see if it helps explain it: http://msdn.microsoft.com/en-us/library/ms181055%28v=SQL.100%29.aspx
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 27, 2012 at 1:21 pm
GSquared (2/27/2012)
MyDoggieJessie (2/27/2012)
But fact that the execution plans for the procs are often cached and more likely to be reused isn't though. This isn't an option when running ad hoc select statements...is it???It depends on the parameterization of the ad hoc query.
Take a look at this and see if it helps explain it: http://msdn.microsoft.com/en-us/library/ms181055%28v=SQL.100%29.aspx
I agree here - depends on the parameterization involved by each of the statements being executed, if they're static then they can be reused.
February 27, 2012 at 1:25 pm
raghuveer126 (2/24/2012)
My team mate suggested me its beter that SQL query be in the report rather than using SP. But i have parameters and how can i put sql in my report. he says there are more than 250 reports so when i write sp its not safe.
Patiently explain to him that leaving the pure SQL up on the report is not safe, as you've exposed a reporting (and thus, generically accessible) login to far too much power at the database level. The Reporting logins should only have execute permission on procs, never access (even read access) to the tables, as that implies schema visibility.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 27, 2012 at 1:51 pm
Thank you guys. I will try to convince him sp is a good thing.
February 27, 2012 at 1:57 pm
Also - stored procedures typically provide better maintainability and reusability
March 6, 2012 at 4:30 am
The only scenario where letting the query in the report is better than writing a SP is when the DB lies in a third party server and you have no access to it and is a lot easier to change things in the app.
In any other concern SP is better, safer, most reliable and easier do maintain.
March 12, 2012 at 3:12 am
also used the query in the report initially, till I worked on a team that used SPs, much easier.
Ian Cockcroft
MCITP BI Specialist
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply