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: Yesterday @ 12:46 PM
Points: 1,776, Visits: 3,222
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: Yesterday @ 9:50 PM
Points: 946, Visits: 1,773
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 9:33 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 10:38 AM
Points: 1,945, Visits: 3,004
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


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1284650
Posted Monday, April 16, 2012 9:37 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 10:38 AM
Points: 1,945, Visits: 3,004
Your mindset is still back in the days of COBOL, FORTRAN and BASIC with a monolithic unit of work that locks data and code into one module. Learn to think in terms of a tiered architecture.

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1284651
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, September 3, 2014 11:44 AM
Points: 1,127, Visits: 1,592
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: Yesterday @ 9:57 AM
Points: 2,854, Visits: 5,120
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: Yesterday @ 9:57 AM
Points: 2,854, Visits: 5,120
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
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 9:30 AM
Points: 577, Visits: 3,416
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: Yesterday @ 9:57 AM
Points: 2,854, Visits: 5,120
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
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse