January 11, 2010 at 9:33 am
I've been having a look at a 3rd party database application we administer.
A lot of their SPs have PRINT statements in there. I've only ever used those for debugging purposes during development when I've been running the procs directly in Query Analyser - is there a legitimate reason for them to be in there when they are being called by an application?
If not, does it matter that they're in there?
Thanks
January 11, 2010 at 9:44 am
It might matter, if the application somehow ends up receiving those as output from the stored procedure. I've seen problems like that with procs that don't have "set nocount on" in them. Most likely, though, it doesn't matter.
- 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
January 11, 2010 at 9:49 am
Thank you!
I understand why SET NOCOUNT ON not being in the SP could be problematic with the potential extra data travelling down the wire, but was unsure whether PRINT could have the same sort of effect
If they get errors in the application then that's the 3rd party's issue 🙂 , I'm just generally looking into performance
January 12, 2010 at 7:01 am
Shouldn't have any measurable effect on performance.
- 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
January 12, 2010 at 7:02 am
January 12, 2010 at 7:53 am
PRINT statements have a similar effect to omitting SET NOCOUNT ON. Whether the effect is big enough to be problematic depends on how many there are, how often the procedure is executed, and how complex it is.
Quite apart from anything else, it is probably bad style. If there is a need to include PRINT statements in a procedure (perhaps for debugging) one approach is to add an optional BIT parameter with a default of zero which determines whether to execute the PRINT statements or not.
In that case, your PRINT statements would change to something like:
IF @Debug = 1 PRINT 'Some debugging information';
That would be my preference anyway (short of removing them entirely).
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 13, 2010 at 4:51 am
Thanks Paul
There are a lot of PRINT statements within cursor loops, and all over the place generally - and there is certainly no @debug flag.
Also SET NOCOUNT ON is missing from every stored procedure I've looked at so far...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply