Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


use print statement in stored procedure


use print statement in stored procedure

Author
Message
sqlfriends
sqlfriends
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2041 Visits: 3853
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
CapnHector
CapnHector
SSC Eights!
SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)

Group: General Forum Members
Points: 917 Visits: 1789
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
vinu512
vinu512
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1149 Visits: 1618
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 ;-)
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3040 Visits: 5478
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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3040 Visits: 5478
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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
davidandrews13
davidandrews13
SSC Eights!
SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)

Group: General Forum Members
Points: 824 Visits: 4447
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?
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3040 Visits: 5478
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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Zoran Jankovic
Zoran Jankovic
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search