PRINT SELECT Statement messages within WHILE LOOP or BATCH of statement is not displayed immediately after it’s execution

Are you facing the problem where the PRINT/SELECT statements messages are not being displayed like the one’s explained in the below two scenario’s? Let us go through these scenario’s and also see how to solve them.

PROBLEM: PRINT/SELECT Statement values within a WHILE LOOP are not displayed when they are executed instead they are displayed at the end of the WHILE loop.

SET NOCOUNT ON GO DECLARE @iteration AS INT SET @iteration = 1 WHILE(@iteration<=10) BEGIN     SELECT 'Start of Iteration ' + CAST(@iteration AS VARCHAR)     WAITFOR DELAY '00:00:01'     PRINT 'End Of Iteration ' + CAST(@iteration AS VARCHAR)     SET @iteration = @iteration + 1 END

In the above script we are going through 10 iteration and each iteration has delay of 1 second. And during each iteration we are displaying messages using PRINT and SELECT statements. Here if we observe the execution of the above script, all the PRINT/SELECT statement messages are displayed at the end of loop i.e. after 10 seconds instead of it being displayed during each iteration (i.e. when it is executed).

As shown in the below image even after 8 seconds of execution of the above script not even a single message’s of the PRINT/SELECT are displayed.

SOLUTION: The reason why the PRINT/SELECT Statement Messages within a batch are not displayed is because Sql Server BUFFERS the output.

Below are the two way of solving this issue. Here I am using the RAISERROR WITH NOWAIT, and severity level is 10 which is sufficient enough for the Sql Server to consider it as a message instead of it being an error. RAISERROR WITH NOWAIT statement flushes all the buffered messages.

Note this works fine for the first 500 RAISERROR WITH NOWAIT statement’s only. After that SQL Server starts flushing 50 messages at a time.

SET NOCOUNT ON GO DECLARE @iteration AS INT,@message as VARCHAR(100) SET @iteration = 1 WHILE(@iteration<=10) BEGIN     SET @message =  'Start of Iteration ' + CAST(@iteration AS VARCHAR)     RAISERROR( @message, 10,1) WITH NOWAIT     WAITFOR DELAY '00:00:01'     SET @message =  'End Of Iteration ' + CAST(@iteration AS VARCHAR)     RAISERROR( @message, 10,1) WITH NOWAIT     SET @iteration+=1 END

OR You can write

SET NOCOUNT ON GO DECLARE @iteration AS INT SET @iteration = 1 WHILE(@iteration<=10) BEGIN     SELECT 'Start of Iteration ' + CAST(@iteration AS VARCHAR)     WAITFOR DELAY '00:00:01'     PRINT 'End Of Iteration ' + CAST(@iteration AS VARCHAR)     SET @iteration+=1       RAISERROR('',10,1) WITH NOWAIT END

RESULT: Below image demonstrates that the messages are displayed as and when the corresponding statement are executed:

PROBLEM: PRINT/SELECT Statement values within a BATCH of statements are not displayed when they are executed instead they are displayed at the end of the execution of the BATCH.

PRINT 'PRINT MESSAGE' SELECT 'SELECT MESSAGE'   WAITFOR DELAY '00:00:10' GO

As shown in the below image even after 8 seconds of execution of the above script the PRINT/SELECT messages are not displayed.

Solution

PRINT 'PRINT MESSAGE'  SELECT 'SELECT MESSAGE'  RAISERROR('',10,1) WITH NOWAIT  WAITFOR DELAY '00:00:10'  GO -- ————-OR————- RAISERROR('PRINT MESSAGE',10,1) WITH NOWAIT RAISERROR('SELECT MESSAGE',10,1) WITH NOWAIT WAITFOR DELAY '00:00:10' GO

Looping through table records in Sql Server

This article lists out extensive list of example scripts for looping through table records one row at a time. This article covers the examples for the following scenario’s for looping through table rows

1. Looping column having no gaps/duplicate values
2. Looping column having gaps
3. Looping column having duplicates

[ALSO READ] WHILE loop in Sql Server

To understand the looping of the table records in the above listed scenarios, let us first create a temporary table #Student as shown in the below image with sample data using the following script.

USE TEMPDB GO CREATE TABLE #Student (Id INT, Name NVARCHAR(100), Status TINYINT) GO INSERT INTO #Student ( Id, Name, Status) Values (1, 'Munesh sharma', 0),         (2, 'Rahul sharma', 0),         (3, 'Roy len', 0) GO

The below examples illustrates how we can loop through table records in various ways. And also highlights the problem if any. Please go through all the examples before deciding on using one particular approach.

Example 1: Looping column having no gaps/duplicate values

Approach 1: Looping through table records with static loop counter initialization

DECLARE @LoopNo INT = 1, @MaxStudentId INT = 3 ,         @StudentName NVARCHAR(100) WHILE(@LoopNo <= @MaxStudentId) BEGIN    SELECT @StudentName = Name    FROM #Student WHERE Id = @LoopNo    PRINT @StudentName     SET @LoopNo  = @LoopNo  + 1        END

In this example the loop running variable @LoopNo and the maximum loop counter variable @MaxStudentId values are initialized with a static value.

Note: This approach of looping through table rows doesn’t work if the looping column (i.e. in this case Id column of the #Student table) values have gaps or if it has duplicate values

Approach 2: Looping through table records with dynamic loop counter initialization

DECLARE @LoopNo INT , @MaxStudentId INT,         @StudentName NVARCHAR(100) SELECT @LoopNo = min(id) , @MaxStudentId = max(Id) FROM #Student WHILE(@LoopNo IS NOT NULL       AND @LoopNo <= @MaxStudentId) BEGIN    SELECT @StudentName = Name    FROM #Student WHERE Id = @LoopNo        PRINT @StudentName     SET @LoopNo  = @LoopNo  + 1        END

In this example the loop running variable @LoopNo and the maximum loop No variable @MaxStudentId values are initialized dynamically.

Note: This approach of looping through table rows doesn’t work if the looping column (i.e. in this case Id column of the #Student table) values have gaps or if it has duplicate values

Example 2: Looping through table records where looping column has gaps

Issue with example 1’s approach 1 and 2: These example approaches are assuming that looping column values doesn’t have any gap in it. Let us see what is the output of the example 1’s approach 1 and 2 if we have gaps in the looping column value.

To create a gap, delete Student record from the #Student table with id = 2 by the following script:

DELETE FROM #Student WHERE Id = 2 GO select * from #Student

Now let us run the example 1’s approach 1 and 2 script on #Student table which is having gap in the Id column value (i.e. record with id column value 2 is missing).

From the above result it is clear that the example 1’s approach 1 and 2 script will not work in the scenarios where we have gap in the looping tables column values.

This problem can solved in multiple ways, below are two such example approaches. I would prefer the first approach.

Example-2

Approach 1: Looping through table records where looping column has gaps in the value

DECLARE @LoopNo INT , @MaxStudentId INT,         @StudentName NVARCHAR(100) SELECT @LoopNo = min(id) , @MaxStudentId = max(Id) FROM #Student WHILE ( @LoopNo IS NOT NULL         AND  @LoopNo <= @MaxStudentId) BEGIN    SELECT @StudentName = Name FROM #Student    WHERE Id = @LoopNo    PRINT @StudentName     SELECT @LoopNo  = min(id) FROM #Student    WHERE Id > @LoopNo END

From the above result it is clear that this script works even when we have gaps in the looping column values.

Note: This approach of looping through table rows doesn’t work if the looping column (i.e. in this case Id column of the #Student table) has duplicate values

Approach 2: Looping through table records where looping column has gaps in the value

DECLARE @LoopNo INT , @MaxStudentId INT,         @StudentName NVARCHAR(100) SELECT @LoopNo = min(id) , @MaxStudentId = max(Id) FROM #Student WHILE ( @LoopNo IS NOT NULL         AND  @LoopNo <= @MaxStudentId) BEGIN    SELECT @StudentName = Name    FROM #Student WHERE Id = @LoopNo    --To handle gaps in the looping column value    IF(@@ROWCOUNT = 0 )    BEGIN      SET @LoopNo  = @LoopNo  + 1      CONTINUE    END    PRINT @StudentName     SET @LoopNo  = @LoopNo  + 1        END

From the above result it is clear that this script works even when we have gaps in the looping column values.

Note: This approach of looping through table rows doesn’t work if the looping column (i.e. in this case Id column of the #Student table) has duplicate values

Example 3: Looping through table records where looping column having duplicates

To create a duplicate record, insert one more Student record to the #Student table with id = 1 by the following script:

INSERT INTO #Student ( Id, Name, Status) Values (1, 'Anshuman Sharma', 0) Go select * from #Student

Now let us run the example 2’s approach 1 and 2 script on #Student table which is having duplicate Id column values (i.e. there are two records with with Id column value as 1)

Print ' example 2 - approach 1' DECLARE @LoopNo INT , @MaxStudentId INT,         @StudentName NVARCHAR(100) SELECT @LoopNo = min(id) , @MaxStudentId = max(Id) FROM #Student WHILE ( @LoopNo IS NOT NULL         AND  @LoopNo <= @MaxStudentId) BEGIN    SELECT @StudentName = Name FROM #Student    WHERE Id = @LoopNo    PRINT @StudentName     SELECT @LoopNo  = min(id) FROM #Student    WHERE Id > @LoopNo END Go Print ' example 2 - approach 2' DECLARE @LoopNo INT , @MaxStudentId INT,         @StudentName NVARCHAR(100) SELECT @LoopNo = min(id) , @MaxStudentId = max(Id) FROM #Student WHILE ( @LoopNo IS NOT NULL         AND  @LoopNo <= @MaxStudentId) BEGIN    SELECT @StudentName = Name    FROM #Student WHERE Id = @LoopNo    --To handle gaps in the looping column value    IF(@@ROWCOUNT = 0 )    BEGIN      SET @LoopNo  = @LoopNo  + 1      CONTINUE    END    PRINT @StudentName     SET @LoopNo  = @LoopNo  + 1        END

From the above result it is clear that the example 2’s approach 1 and 2 script will not work in the scenarios where we have duplicates in the looping column. Here only one record of the Student with id =1 is displayed and other record is skipped. This problem can solved in multiple ways, below are two such example approaches.

Approach 1: Looping through table records where looping column has duplicate values

SET NOCOUNT ON DECLARE @LoopNo INT , @MaxStudentId INT,         @StudentName NVARCHAR(100) SELECT @LoopNo = min(id) , @MaxStudentId = max(Id) FROM #Student   WHILE  ( @LoopNo IS NOT NULL          AND  @LoopNo <= @MaxStudentId) BEGIN    UPDATE TOP(1) #Student    SET  Status = 1, @StudentName = Name    WHERE Id = @LoopNo  AND Status = 0      PRINT @StudentName       SELECT @LoopNo  = min(id) FROM #Student     WHERE Id >= @LoopNo AND Status = 0 END

In this approach using the Status column to mark the records which are already processed. And also the update statement is used to update the status and also get the row values and one more thing is in Update using the TOP statement to update only one record at a time.

Approach 2: Looping through table records where looping column has duplicate values by inserting records into another temp table with identity column

--Create another temp table with identity column CREATE TABLE #StudentCopy (LoopId INT IDENTITY(1,1),   Id INT, Name NVARCHAR(100), Status TINYINT) --Copy data to the table with identity column INSERT INTO #StudentCopy(Id, Name, Status) SELECT Id, Name, Status FROM #Student DECLARE @LoopNo INT , @MaxStudentId INT,         @StudentName NVARCHAR(100) SELECT @LoopNo = min(LoopId),@MaxStudentId = max(LoopId) FROM #StudentCopy WHILE ( @LoopNo IS NOT NULL         AND  @LoopNo <= @MaxStudentId) BEGIN    SELECT @StudentName = Name    FROM #StudentCopy  WHERE LoopId = @LoopNo    PRINT @StudentName     SELECT @LoopNo  = min(LoopId)    FROM #StudentCopy  WHERE LoopId > @LoopNo END