如何查看运行SQL存储过程的进度？

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?

假设每个delete语句需要很长时间才能删除，但是我喜欢在SQL Management Studio中运行它时看到这个存储过程的进度。换句话说，我喜欢看PRINT语句的输出，看看我在任何给定时间的位置。但是，似乎我只能在ENTIRE运行结束时看到PRINT输出。有没有办法让它能够实时看到PRINT输出？如果没有，有没有其他方法可以看到正在运行的存储过程的进度？

2 个解决方案

#1

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:

如果您使用严重性为10或更低的RAISERROR，并使用NOWAIT选项，它将立即向客户端发送信息性消息：

RAISERROR ('Deleting old data Part 1/3' , 0, 1) WITH NOWAIT

RAISERROR（'删除旧数据第1/3'，0,1）与NOWAIT

#2

Yes you should be able to get the message to print immediately if you use RAISERROR:

是的，如果您使用RAISERROR，您应该能够立即打印消息：

RAISERROR('Hello',10,1) WITH NOWAIT

RAISERROR（'你好'，10,1）和NOWAIT

#1