T-SQL 101: 94 Returning messages to the client by using PRINT and RAISERROR in T-SQL

There are times when you’re writing in any programming language, that you want to output messages back to the client. In T-SQL, the statement provided for that is the PRINT statement.

PRINT is simple enough to use. You give it a  string to return and it prints that string.

It’s different to the SELECT statement, because the SELECT statement returns a rowset (i.e. a set of rows).

In the main image above, you can see a script with both a PRINT and a SELECT. When that script is executed, two things happen. The output of the SELECT statement is returned in the Results tab in SQL Server Management Studio as below:

But notice that the PRINT output isn’t there. Instead, it’s returned in the Messages tab as below:

Using RAISERROR instead of PRINT

PRINT is actually a specific instance of the RAISERROR statement. Instead of using PRINT, you could use RAISERROR like this:

Notice that the output is identical. The first parameter to RAISERROR is the message, the second is the severity, and the third is the state. In this case, any severity from 1 to 10 would have worked. You can just make the state be 1 and ignore it until we discuss RAISERROR in much more detail later.

RAISERROR and NOWAIT

So why would you ever use RAISERROR instead of PRINT? After all, PRINT is simpler.

The answer is related to when the messages are returned. For example, if you put PRINT statements in a stored procedure, it’s only after the stored procedure completes that you’ll see the output.

If however, you use the NOWAIT option in RAISERROR, the messages come back immediately.

This can be very useful when you are debugging code.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2021-04-12