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
|
EmoticonEmoticon