Menu
Consider the following stored procedure..
CREATE PROCEDURE SlowCleanUp (@MaxDate DATETIME) AS BEGIN PRINT 'Deleting old data Part 1/3...' DELETE FROM HugeTable1 where SaveDate < @MaxDate PRINT 'Deleting old data Part 2/3...' DELETE FROM HugeTable2 where SaveDate < @MaxDate PRINT 'Deleting old data Part 3/3...' DELETE FROM HugeTable3 where SaveDate < @MaxDate PRINT 'Deleting old data COMPLETED.' END Let's say that each delete statement take a long time to delete, but I like to see the progress of this stored procedure when I'm running it in SQL Management Studio. In other words, I like to see the the output of the PRINT statements to see where I'm at any given time. However, it seems that I can only see the PRINT outputs at the end of the ENTIRE run. Is there a way to make it so that I can see the PRINT outputs at real time? If not, is there any other way I can see the progress of a running stored procedure?
573
Return Value in SQL Server Stored Procedure In default, when we execute a stored procedure in SQL Server, it returns an integer value and this value indicates the execution status of the stored procedure. The 0 value indicates, the procedure is completed successfully and the non-zero values indicate an error.
There is no feature built in to SQL Server to check the status of a stored procedure but you can write something that will do something similar. In our case, we created a logging function that post a message after each process within a stored proc.
If you use RAISERROR with a severity of 10 or less, and use the NOWAIT option, it will send an informational message to the client immediately:
RAISERROR ('Deleting old data Part 1/3' , 0, 1) WITH NOWAIT
64
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
