This article explores the SQL Server PRINT statements, and its alternative SQL Server RAISEERROR statements to print messages in a query.
Introduction
Suppose you execute a query with multiple steps. We want to get a message once each step completes. It helps to track the query progress. Usually, we use the SQL PRINT statement to print corresponding messages or track the variable values while query progress.
We also use interactions or multiple loops in a query with a while or for a loop. We can also use the SQL PRINT statement to track the iteration.
We use the SQL Server PRINT statement to return messages to the client. We specify the message as string expressions input. SQL Server returns the message to the application.
In this article, we will explore several use cases of SQL PRINT statement, its limitations, and alternatives of SQL PRINT statements.
Example 1: SQL Server PRINT statement to print a string
It is the simplest example. Execute the following query in SSMS, and it returns the following message in the output:
|
1 |
PRINT 'My Name is Rajendra Gupta'; |
In SSMS, we get the PRINT statement output in SSMS message window as shown in the above image.
Example 2: PRINT statement to print a variable value
We can use the SQL PRINT statement to print a variable value as well. Let’s define a message in a variable and later print this message:
|
1 2 |
DECLARE @Msg VARCHAR(300)= 'My Name is Rajendra Gupta'; PRINT @Msg; |
Example 3: SQL Server PRINT statement to print an integer value
We can use the SQL PRINT statement to print an integer value, as shown below:
|
1 2 |
DECLARE @a INT = 1000 PRINT @a |
We can specify only CHAR, NCHAR, VARCHAR or NVARCHAR data types in the PRINT statement. In this case, it implicitly converts an integer value to the VARCHAR data type internally.
Let’s use another example with an integer variable and a string in the PRINT statement. You get an error message in data type conversion because SQL Server is trying to convert the varchar data type to integer. In data type precedence, the integer data type has high precedence than the varchar data type:
|
1 2 |
DECLARE @a INT = 1000 PRINT 'Your queue no is ' + @a |
We explicitly convert the integer data type to varchar using the SQL CAST statement:
|
1 2 |
DECLARE @a INT= 1000; PRINT 'Your queue no is ' + CAST(@a AS VARCHAR(10)); |
We can use the SQL CONCAT function as well, and it automatically does data type conversion for us. In the following query, we get the output using the CONCAT function similar to the CAST operator:
|
1 2 |
DECLARE @a INT= 1000; PRINT CONCAT('Your queue no is : ',@a) |
Example 4: SQL Server PRINT statement with XML type variable value
We can use XML data type as well with the PRINT statement, but it requires data conversion.
As shown in the following output, we cannot directly use an XML variable in the PRINT statement. It gives an error message that implicit conversion from XML to nvarchar is not allowed:
|
1 2 |
DECLARE @a XML = '<CustomerID="1" CustomerName="Rajendra"/>' PRINT @a |
We can use SQL CAST or CONVERT function explicitly and get the required output:
|
1 2 |
@a XML = '<Customer id="1" Name="Rajendra"/>' PRINT CAST(@a AS VARCHAR(100)) |
Example 5: SQL Server PRINT Statement with IF conditions
Let’s use the PRINT statement to print the message satisfied in the IF condition. In this example, the variable @a contains a string. The IF condition checks for the string and prints message satisfying the condition:
|
1 2 3 4 5 6 |
DECLARE @a VARCHAR(100)= 'Mango'; IF @a = 'Mango' PRINT N'It is a Fruit'; ELSE PRINT N'It is a vegetable'; GO |
Example 6: PRINT Statement with NULL values
We cannot print NULL in the message using the SQL PRINT statement. The following query does not return any result:
|
1 |
Print NULL |
Let’s use the following query that contains a variable with NULL values. In the PRINT statement, we use a string along with this variable, and it does not return any message. The concatenation of a string and the variable @a (NULL) that does not return any output:
|
1 2 |
DECLARE @a NVarChar(100)= NULL PRINT 'Hello' + @a |
Example 7: SQL Server PRINT Statement in a WHILE loop
As stated earlier, many times, we require knowing information about each iteration when query running in a loop such as WHILE or FOR.
The following query uses WHILE loop and prints a message about each iteration:
|
1 2 3 4 5 6 7 |
DECLARE @a INT; SET @a = 1; WHILE(@a < 10) BEGIN PRINT CONCAT('This is Iteration no:' , @a) SET @a = @a + 1; END; |
It gives the following output. We can use a Print statement with an appropriate message and track query progress.
Limitations of SQL Server PRINT statement
In the previous example, we saw the use of cases of SQL PRINT statements. We have the following limitations with PRINT as well:
- We need to use CAST, CONVERT or CONCAT functions in the PRINT statement with variables
- We cannot see a PRINT statement in SQL PROFILER
- The PRINT statement does not return the message immediately; it buffers the output and displays them
Let’s elaborate point no 3 and see its alternatives.
Execute the following query that contains two PRINT statements. Looking at the query, you might think of output in the following form:
- It gives the message from the first PRINT statement
- Waits for 5 seconds
- It gives the message for a second PRINT statement
- Waits for another 5 seconds
|
1 2 3 4 |
PRINT 'My Name is Rajendra Gupta'; WAITFOR DELAY '00:00:05'; PRINT 'You are reading article on SQL PRINT statement'; WAITFOR DELAY '00:00:05'; |
In the following GIF image, you can note that it prints message from both SQL PRINT statements together after 10 seconds:
Let’s use another example and see PRINT statement behavior. In this, we want to print the message as soon as iteration completes:
|
1 2 3 4 5 6 7 8 |
DECLARE @a INT; SET @a = 1; WHILE(@a < 15) BEGIN PRINT CONCAT('This is Iteration no:' , @a) WAITFOR DELAY '00:00:01' SET @a = @a + 1; END; |
In the output, we can note that all messages output from PRINT statements appear together once the execution completes. The loop executes 14 times and waits 1 second on each execution. Therefore, we get output after 14 seconds for all PRINT statements:
SQL Server buffers messages for multiple PRINT statements and displays them together.
It is not the desired output, and in any case, it might not be useful as you cannot track query progress in real-time.
SQL Server RAISERROR statement
We can use an alternative to the SQL PRINT statement that is RAISERROR.
We require a few arguments in RAISERROR statements.
- Message – It is the message that we want to print
- Severity – It is a number between 0 and 25 and defines the severity of the messages. It treats the message differently with each severity. We will see a few examples of it in this article
- State – It is a number between 0 and 255 to distinguish one error from another. It is good to use value 1 for this article
We need to use RAISERROR statement with NOWAIT clause; otherwise, it shows the same behavior as of SQL PRINT statement:
|
1 2 3 4 |
RAISERROR('My Name is Rajendra Gupta', 0, 1) WITH NOWAIT; WAITFOR DELAY '00:00:05'; RAISERROR('You are reading article on SQL PRINT statement', 0, 1) WITH NOWAIT; WAITFOR DELAY '00:00:05'; |
In the following output with SQL Server RAISERROR statements, we can note the following:
- It gives the message from the first PRINT statement
- Waits for 5 seconds
- It gives the message for the second PRINT statement
- Waits for another 5 seconds
Previously we used severity 0 in the RAISERROR statement. Let’s use the same query with severity 16:
|
1 2 3 4 |
RAISERROR('My Name is Rajendra Gupta', 16, 1) WITH NOWAIT; WAITFOR DELAY '00:00:05'; RAISERROR('You are reading article on SQL PRINT statement', 16, 1) WITH NOWAIT; WAITFOR DELAY '00:00:05'; |
In the output, we can see the message appears in red, and it shows the message as an error instead of a regular message. You also get message id, level, and state as well:
Let’s execute the following query with severity 1 and severity 16. Severity 1 shows the message with additional information, but it does not show the message as an error. You can see the text color in black.
Another SQL Server RAISERROR shows the output message as an error:
|
1 2 3 4 |
RAISERROR('My Name is Rajendra Gupta', 1, 1) WITH NOWAIT; WAITFOR DELAY '00:00:05'; RAISERROR('You are reading article on SQL PRINT statement', 16, 1) WITH NOWAIT; WAITFOR DELAY '00:00:05'; |
We cannot use SQL Server RAISERROR directly using the variables. We get the following output that is not the desired output:
|
1 2 3 4 5 6 7 |
DECLARE @a INT; SET @a = 1; WHILE(@a < 15) BEGIN RAISERROR('This is Iteration no:', @A, 0, 1) WITH NOWAIT; SET @a = @a + 1; END; |
It prints the message but does not show the value of the variable:
We need to use the C-style print statements with RAISERROR. The following query shows the variable with the RAISERROR. You can notice that we use %s and %d to print a string and integer value:
|
1 2 3 4 5 6 7 8 |
DECLARE @a INT; SET @a = 1; DECLARE @S VARCHAR(100)= 'This is iteration no'; WHILE(@a < 5) BEGIN RAISERROR('%s:%d', 0, 1, @s, @a) WITH NOWAIT; SET @a = @a + 1; END; |
We get the instant output in SQL Server RAISERROR along with WITH NOWAIT statement as per our requirement and does not use buffer to display output once the query finishes:
You might confuse between RAISERROR statement that it is for raising error messages in SQL Server. We can use it as an alternative to the SQL PRINT statement as well. Usually, developers use PRINT statements only to gives messages in a query. You should explore RAISERROR statements for your queries, stored procedures.
Conclusion
In this article, we explored the SQL Server PRINT statement and its usages to track useful milestones in a query. We also learned the limitations of it along with alternative solution RAISERROR statement in SQL Server. SQL Server RAISERROR gives you a great advantage to control output buffer behavior.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023