CODEWITHSUNDEEP
sqlsql-server-2005
w0051977
w0051977

Reputation: 15817

Flush print statements to client (SQL Server Management Studio)

Please see the code below:

USE [Test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Test]
AS
BEGIN
begin tran
begin try
    SET NOCOUNT ON;
    DECLARE @ID int
    DECLARE @Count int
    set @Count=0
    DECLARE Employee_Cursor CURSOR FOR
SELECT id
FROM Person3;
OPEN Employee_Cursor;
FETCH NEXT FROM Employee_Cursor INTO @ID
WHILE @@FETCH_STATUS = 0
   BEGIN    
        WAITFOR DELAY '000:00:01'
        SET @Count = @Count+1
        Print @Count        
      FETCH NEXT FROM Employee_Cursor INTO @ID;
   END;
   commit tran
CLOSE Employee_Cursor;
DEALLOCATE Employee_Cursor;
end try
begin catch
ROLLBACK tran
end catch
END

The stored procedure runs and then the following output is flushed to the client all at once (because there are three records in: person3):

1
2
3

How do you flush the output to the client on each iteration of the cursor? I am looking for an answer that is compatible with SQL Server 2005.

I have tried usinbg the RAISEERROR statement as desribed here: How do I flush the PRINT buffer in TSQL?, however all the statements still appear at the end.

Upvotes: 1

Views: 5361

Answers (1)

Alexandre N.
Alexandre N.

Reputation: 2802

Use the combination of a RAISERROR severity of 0 to 10 and the WITH NOWAIT clause for a statement that sends output to the Messages windows immediately.

RAISERROR ('Now that''s what I call a message!', 0, 1) WITH NOWAIT

Reference: https://www.mssqltips.com/sqlservertip/1660/using-the-nowait-option-with-the-sql-server-raiserror-statement/

Upvotes: 2

Related Questions