CODEWITHSUNDEEP
sqlt-sqlsql-server-2008-r2
Vaibhav
Vaibhav

Reputation: 7008

Why do people use RaiseError instead of Print in T-SQL

I have tried both the below queries and they perform in same time. But still I have seen RaiseError used in many stored procedures instead of print.

Query 1:

BEGIN
    WAITFOR DELAY '00:00:03.00'

    PRINT 'DELAY 1 HAS ENDED'

    WAITFOR DELAY '00:00:03.00'

    PRINT 'DELAY 2 HAS ENDED'
END

Query 2:

BEGIN
    WAITFOR DELAY '00:00:03.00'

    RAISERROR ('DELAY 1 HAS ENDED', 10,1) WITH NOWAIT

    WAITFOR DELAY '00:00:03.00'

    RAISERROR ('DELAY 2 HAS ENDED', 10,1) WITH NOWAIT

END

Both give the desired output only after 6 seconds (I have checked this in SQL Server 2008 R2)

Upvotes: 10

Views: 7366

Answers (4)

sqlhead
sqlhead

Reputation: 21

There is a return statement buffer that causes PRINT statements to only flush when full (or SQL Server deigns to do so). RAISERROR(x, y, z) WITH NOWAIT either does not use that buffer or appears to force a flush of that buffer after adding its message.

Other than this and the embedded variable behavior mentioned by Philippe in his answer, there appears to be no real functional difference between low-severity RAISERROR and PRINT.

Here's a short 2018 post by Louis Davidson (@drsql) with more code samples and links to other posts with even more detail on this: https://www.red-gate.com/simple-talk/blogs/outputting-status-t-sql-code/.

As for me, I now use RAISERROR(@msg, 0, 42) WITH NOWAIT for my batch script progress outputs after getting frustrated waiting for PRINT statements to come through.

Upvotes: 2

Philippe Raemy
Philippe Raemy

Reputation: 433

The advantage of RAISERROR over PRINT is that you may embed variables values within the message without having to bother with CASTand CONVERT. For instance:

 BEGIN
     DECLARE @m INT = 0

     WAITFOR DELAY '00:00:01.00'
     SET @m += 1;
     RAISERROR ('DELAY %d HAS ENDED', 10, 1, @m)
 
     WAITFOR DELAY '00:00:01.00'     
     SET @m += 1;
     RAISERROR ('DELAY %d HAS ENDED', 10, 1, @m)
 
 END

This will produce same output in both examples above, except it will insert the value of variable into the message. With a PRINT you have to do:

PRINT 'DELAY ' + CONVERT(VARCHAR(5), @m) + ' HAS ENDED'

...which is possible but cumbersome...

Upvotes: 13

Izulien
Izulien

Reputation: 413

We use RAISERROR to track actual errors a bit easier. This comes in handy since you can set severity for items and you can see the results right away in the Messages section.

PRINT is used for a really cheap/quick debugger in a multi-step process that shows how far we actually get when I don't actually expect immediate feedback.

Upvotes: 1

Scoregraphic
Scoregraphic

Reputation: 7200

I have the same server version as you did, and Query 2 prints the first result after 3 seconds (as expected). You have to switch to the Message tab on your own in Microsoft SQL Server Management Studio to see the message.

Upvotes: 5

Related Questions