In Sql Server PRINT statement can be used to return message to the client. It takes string expression as input and returns string as a message to the application. In case of SSMS the PRINT statement output is returned to the Messages window and in case applications PRINT statement output is returned as an informational error message to the client application.
Basically we use PRINT statement for troubleshooting the code by displaying the message or displaying variable value etc.
Let us understand PRINT statement with extensive list of examples
Example 1: PRINT statement printing/returning a string literal
PRINT 'Hello World!'
RESULT:
From the above result we can see that in case of Sql Server Management Studio, the PRINT statement output is returned to the Messages tab.
Example 2: PRINT statement printing a Sql Server variable value
DECLARE @WelcomeMsg VARCHAR(100) = 'Hello World!' PRINT @WelcomeMsg
RESULT:
Example 3: PRINT statement printing a Function output
Let us print built-in function GETDATE() return value
PRINT GETDATE()
RESULT:
PRINT Statement Input and Return Data Type
The input to the PRINT statement can be either of CHAR, NCHAR, VARCHAR or NVARCHAR data type. If input passed to it is other than these specified data types then it tries to implicitly convert it to one of these data types. And if input is of type VARCHAR(MAX) or NVARCHAR(MAX) then it is truncated to datatypes VARCHAR(8000) or NVARCHAR(4000). The return type of the PRINT statement is either VARCHAR or NVARCHAR depends on the type of the input.
Example 4: Implicit and Explicit Data Type conversion in PRINT statement
As explained above PRINT statement expects string input, if other data type is passed it will try to do the implicit conversion of the data type. Let us understand this with couple of examples:
Example 4.1: PRINT statement displaying integer variable value
DECLARE @I INT = 100 PRINT @I
RESULT:
From the above result we can see that the integer variable value passed to the PRINT statement is implicitly converted.
Example 4.2: PRINT statement printing XML type variable value
DECLARE @value XML = '<Employee id="1" Name="Basavaraj"/>' PRINT @value
RESULT:
Msg 257, Level 16, State 3, Line 2
Implicit conversion from data type xml to nvarchar is not allowed. Use the CONVERT function to run this query.
From the above result we can see that the XML type variables implicit conversion to NVARCHAR type is failed. To solve this issue we can explicitly convert the XML type to VarChar and pass to the PRINT statement.
DECLARE @value XML = '<Employee id="1" Name="Basavaraj"/>' PRINT CAST(@value AS VARCHAR(50))
RESULT:
From the above result it is clear that the input to the PRINT statement must be either of CHAR, NCHAR, VARCHAR or NVARCHAR data type. If input passed to it is other than these specified data types then it tries to implicitly convert it to one of these data types.
Example 5: PRINT statement printing the concatenated result of the string literal and and integer variable value
DECLARE @I INT = 100 PRINT 'Current Number : ' + @I
RESULT:
Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value ‘Current Number :’ to data type int.
From the above result we can see that in this case Sql Server is trying to convert string literal value ‘Current Number : ‘ to integer type (i.e. type of the variable @I) as integer has higher precedence than the VarChar type.
To solve this issue we can explicitly convert the integer variable @I value to VARCHAR type as shown below script by using the CAST statement:
DECLARE @I INT = 100 PRINT 'Current Number : ' + CAST(@I AS VARCHAR(10))
RESULT:
In Sql Server 2012 we have CONCAT funtion which takes care of converting the input to the correct format and then concatenating and returning a string output. We can re-write the above script using CONCAT function as below:
DECLARE @I INT = 100
PRINT CONCAT('Current Number : ',@I)
RESULT:
Example 6: NULL in the PRINT statement
Example 6.1: NULL as input to the PRINT statement
PRINT NULL
RESULT:
From the above result we can see that PRINT statement doesn’t print NULL value
Example 6.2: PRINT statement with string expression which is a concatenation of string literal and a variable whose value is NULL
DECLARE @Name NVarChar(50) PRINT 'Welcome ' + @Name
RESULT:
From the above result we can see that PRINT statement didn’t print any value because the concatenation of a string literal ‘Welcome ‘ and the variable @Name whose value is NULL (because it is not initialized) results to NULL.
How to read PRINT statement out in the .NET code
PRINT statement output is returned as an informational error message to the client application. It is not returned as a regular exception instead it is returned as information error message with severity less than or equal to 10. To read the informational messages returned by the PRINT statement or RAISERROR statement with severity less than or equal to 10, we can add event handler delegate method to the InfoMessage event of the connection object in C# ADO.NET code
SqlConnection conn = new SqlConnection(ConnectionString); conn.InfoMessage += new SqlInfoMessageEventHandler(ProcessInformationalMessage);
And below is the sample Delegate method ProcessInformationalMessage code which is writing the PRINT statement output to the console:
protected static void ProcessInformationalMessage(
object sender, SqlInfoMessageEventArgs args)
{
foreach (SqlError err in args.Errors)
{
Console.WriteLine('Error Number {0}, Error Line {1}, Error Message {2}',
err.Number, err.LineNumber, err.Message);
}
}
In a long running stored procedure or script, if you have added multiple PRINT statement to know progress of the script execution. Then to your surprise usually you will not see these messages till the end of procedure execution. The reason is sql server buffers the PRINT statement output and sends to client once it reaches TDS packet size of 4KB. If you want to instantaneously send the PRINT statement output to the client then you can use RAISERROR statement with NO WAIT as explained in the below article: