Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

use print statement in stored procedure Expand / Collapse
Author
Message
Posted Monday, April 16, 2012 5:07 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, July 1, 2015 4:26 PM
Points: 1,838, Visits: 3,403
Can I use multiple print statement in stored procedure?
I use it for troubleshooting purpose.

For example :

I have multiple delete statements in stored procedure:

Print 'deleting from table1'


Delete from table1 where ID= @ProcessID

Print 'deleting from table2'

Delete from table2 where ID=@processID
Post #1284601
Posted Monday, April 16, 2012 7:14 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, April 17, 2015 2:35 PM
Points: 887, Visits: 1,781
i have always used SELECT 'String you want to display here' and not had a problem with multiple outputs in a stored procedure.


For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler
Post #1284627
Posted Monday, April 16, 2012 11:32 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 1, 2015 3:37 AM
Points: 1,127, Visits: 1,608
Yea. Select works multiple times in an SP.
The difference between using PRINT and SELECT is simply the difference of a SQL DBA's(or developers) thinking and any other developer's thinking.
SQL was designed to be a Relational Set based Language. The best results from SQL can be obtained when it is used in that Relational Set based way.

Select returns(prints) what you want as a column ie: in a relational format.
Whereas, Print would just give you a message.
That is why to someone who works with SQL, PRINT is just like an alert or an Error Message Whereas SELECT is the PRINT.

This makes me remember Jeff Moden's signature....."NO RBAR, NO CURSORS, NO LOOPS.....ONLY SET BASED SOLUTIONS"


Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1284679
Posted Tuesday, April 17, 2012 4:19 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 1, 2015 9:05 AM
Points: 2,924, Visits: 5,373
For the purpose you need, the best way is to use RAISERROR like that:

RAISERROR ('My debugging text', 10,1) WITH NOWAIT

It's the same as PRINT, but it guaranteed to be returned back to client as soon as SQL server executes it.






_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1284792
Posted Tuesday, April 17, 2012 4:26 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 1, 2015 9:05 AM
Points: 2,924, Visits: 5,373
CELKO (4/16/2012)
Yes, but it is bad programming. The PRINT is for debugging and not production code. Think about 100 people calling the same program. You do not want to directly to the end user; you send signals to the invoking host language programs and they interface with the end user.

Your mindset is still stuck ba


It depends! I've used RAISERROR ('My debugging text', 10,1) WITH NOWAIT in production to report progress of slow report generating stored procedure back to client application. Users loved it!
Let say your proc is very complex and goes over multiple steps which take some time. The above technique in conjunction with appropriate application code and sql driver, allows asynchronous status update!


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1284801
Posted Tuesday, April 17, 2012 7:14 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 8:57 AM
Points: 698, Visits: 4,087
Eugene Elutin (4/17/2012)
CELKO (4/16/2012)
Yes, but it is bad programming. The PRINT is for debugging and not production code. Think about 100 people calling the same program. You do not want to directly to the end user; you send signals to the invoking host language programs and they interface with the end user.

Your mindset is still stuck ba


It depends! I've used RAISERROR ('My debugging text', 10,1) WITH NOWAIT in production to report progress of slow report generating stored procedure back to client application. Users loved it!
Let say your proc is very complex and goes over multiple steps which take some time. The above technique in conjunction with appropriate application code and sql driver, allows asynchronous status update!


hi. could you elaborate abit please? i dont quite understand your implementation. are you saying your PRINT RAISERROR gets output to the front end?
Post #1284905
Posted Tuesday, April 17, 2012 7:34 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 1, 2015 9:05 AM
Points: 2,924, Visits: 5,373
RAISERROR ('My debugging text', 10,1) WITH NOWAIT

Will return message back to caller immediately. Using ADO.Net SqlInfoMessage you can subscribe to it and get it back during stored proc asynchronous execution. I've done it around 3 years ago and don't remember exact details. Just remember that it was issue with driver.
Huh, found something:
http://www.codeproject.com/Articles/42266/ADO-NET-Asynchronous-SQL-Calls


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1284915
Posted Tuesday, April 17, 2012 7:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 2, 2014 3:30 PM
Points: 6, Visits: 99
If you want log steps of the SP_ you can use loging into log_table.
You can lo step (which is executed), Getdate(),...
you can add sp_name column to log more than one SP_.

It can slow executing your sp_

option :
declare @mess varchar(200)

set @mess = ' now is executing step...' + convert(varchar(50), getdate(), 121)
RAISERROR (@mess ,10,1) WITH NOWAIT

is better for running long executing scripts

option:
PRINT

is only for fast debugging

that's my opinion (if I understand question right)
Post #1284931
Posted Tuesday, April 17, 2012 8:26 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, April 17, 2015 2:35 PM
Points: 887, Visits: 1,781
zojan (4/17/2012)
If you want log steps of the SP_ you can use loging into log_table.
You can lo step (which is executed), Getdate(),...
you can add sp_name column to log more than one SP_.

It can slow executing your sp_

option :
declare @mess varchar(200)

set @mess = ' now is executing step...' + convert(varchar(50), getdate(), 121)
RAISERROR (@mess ,10,1) WITH NOWAIT

is better for running long executing scripts

option:
PRINT

is only for fast debugging

that's my opinion (if I understand question right)


might be what the op is looking for, the op may also be designing a back end SP that only certian users use. i have several SP's that only the DBA's and network ops guys use so i have select 'info text here' so it will output the useful info to the SSMS query results window using the AS to place a column header of info or error or what ever would make sense. for our production applications we use RAISERROR() for issues that may arrise.



For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler
Post #1284960
Posted Tuesday, April 17, 2012 8:31 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 1, 2015 9:05 AM
Points: 2,924, Visits: 5,373
zojan (4/17/2012)
If you want log steps of the SP_ you can use loging into log_table.
...


Not good option for SQL Server as there is no direct equivalent to autonomous transactions like in Oracle. What if your proc does something in transaction which needs to be rolled back? Your log will be rolled back as well...


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1284963
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse