April 16, 2012 at 5:07 pm
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
April 16, 2012 at 7:14 pm
i have always used SELECT 'String you want to display here' and not had a problem with multiple outputs in a stored procedure.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 16, 2012 at 11:32 pm
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"
April 17, 2012 at 4:19 am
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.
April 17, 2012 at 4:26 am
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!
April 17, 2012 at 7:14 am
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?
April 17, 2012 at 7:34 am
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
April 17, 2012 at 7:53 am
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:
is only for fast debugging
that's my opinion (if I understand question right)
April 17, 2012 at 8:26 am
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:
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 performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 17, 2012 at 8:31 am
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...
April 17, 2012 at 9:06 am
Eugene Elutin (4/17/2012)
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...
It depends of log. I agree it's not a lucky solution (and not in all situations), but for some time (just for looking what's happen, when is prob. to write a good code because of verw dirty data). I mean the best solution in my db's is maybe the worse solution in the other.
I have this (log_table) solutions in use time after time (as shorter as possible) in long term processing (more hours) with large amount of data, when developers have a big problems with dirty data.
April 17, 2012 at 9:21 am
Eugene Elutin (4/17/2012)
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...
I do some logging to variables, table variables are good too as they're not logged back. Of course they aren't save either so eventually you'll want to save or print the contents.
April 17, 2012 at 9:35 am
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.
Thanks all, as above said, this is for DBA use to do data maintenance. I would like to have it dispalyed in the SSMS query reuslts window.
But it is good to learn raiserror() and others to handle errors.
Thanks a lot
April 18, 2012 at 2:40 pm
patrickmcginnis59 (4/17/2012)
Eugene Elutin (4/17/2012)
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...
I do some logging to variables, table variables are good too as they're not logged back. Of course they aren't save either so eventually you'll want to save or print the contents.
+1 ... one of the few redeeming qualities of Table Variables
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 19, 2012 at 1:06 am
Please do not use print statement in your Sp. If some one trys to execute that sp from any other client apart from SSMS it will fail since Print Statement is specific to SSMS only...
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply