Silly PRINT question

Question

Post reply

Silly PRINT question

Raymond van Laake

SSCarpal Tunnel

Points: 4212
More actions
October 15, 2010 at 3:09 am

#225094

Hello gurus,
I have recently been told that adding PRINT statements in a Stored Procedure helps when debugging. I agree with that.
But are there any backdraws? The webpages that rely on these Stored Procedures, will they suffer from a PRINT statement (no afaik)?
Are there performance issues?
Thanks!
Ray

Viewing 6 posts - 1 through 5 (of 5 total)

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

you can use them in debugging , but surely you would remove them before putting them in to production ?

Lowell SSC Guru Points: 323518 More actions · Answer 2

AFAIK there is no issues with leaving print statements in place; I've never seen or heard of an application suffering due to print statements.

even from an application you can read any messages, including print statements and rows affected statements, by adding an event on the SQLConnection object; that's the same way SSMS reads the messages back as well.

I've always left print statements in place when launched into production, as they were there to help confirm the code was doing what it was supposed to do; even code in production gets debugged sometimes.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

WayneS SSC Guru Points: 95460 More actions · Answer 3

steveb. (10/15/2010)
you can use them in debugging , but surely you would remove them before putting them in to production ?

Actually, I create all stored procedures like this:

CREATE PROCEDURE dbo.Test (

@Param1...

@Param2...

@Debug tinyint = 0)

Then, inside the procedure, I use the print statements like:

IF @Debug > 0 PRINT 'My message'

IF @Debug > 1 PRINT 'My Level 2 message'

IF @Debug > 5 SELECT data or variables to show what's going on

In production, just never pass anything to the @Debug parameter. Only use that when running from a query window. By using a tinyint, instead of a bit, I can have different levels of debug messages.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

steveb. SSC-Forever Points: 46733 More actions · Answer 4

WayneS (10/15/2010)
steveb. (10/15/2010)
you can use them in debugging , but surely you would remove them before putting them in to production ?
Actually, I create all stored procedures like this:
CREATE PROCEDURE dbo.Test (
@Param1...
@Param2...
@Debug tinyint = 0)
Then, inside the procedure, I use the print statements like:
IF @Debug > 0 PRINT 'My message'
IF @Debug > 1 PRINT 'My Level 2 message'
IF @Debug > 5 SELECT data or variables to show what's going on
In production, just never pass anything to the @Debug parameter. Only use that when running from a query window. By using a tinyint, instead of a bit, I can have different levels of debug messages.

That seems like a really good idea , But I guess it depends on the policies where you work. At our place all code (mainly Python, C# and T-SQL) is reviewed before going into production and any 'debug' code is flagged for removal. They apply the same policy regardless of language.

Raymond van Laake SSCarpal Tunnel Points: 4212 More actions · Answer 5

Thanks to everyone for comments, I've become a wiser man!

Ray