Share via

  • Article

Using PRINT

The PRINT statement is used to return messages to applications. PRINT takes either a character or Unicode string expression as a parameter and returns the string as a message to the application. The message is returned as an informational error to applications using the SQLClient namespace or the ActiveX Data Objects (ADO), OLE DB, and Open Database Connectivity (ODBC) application programming interfaces (APIs). SQLSTATE is set to 01000, the native error is set to 0, and the error message string is set to the character string specified in the PRINT statement. The string is returned to the message handler callback function in DB-Library applications.

The PRINT statement accepts any character string expression, including character or Unicode constants, a character or Unicode local variable name, or a function that returns a character or Unicode string. PRINT also accepts complex strings built by concatenating two or more constants, local variables, or functions.

Use PRINT to help in troubleshooting Transact-SQL code, check the values of data, or produce reports.

The following example uses PRINT inside an IF statement to return a message when the number of the Hex Nut 17 product falls below 1100.

USE AdventureWorks;
GO
IF (SELECT SUM(i.Quantity)
    FROM Production.ProductInventory i
    JOIN Production.Product p 
    ON i.ProductID = p.ProductID
    WHERE Name = 'Hex Nut 17'
    ) < 1100
    PRINT N'There are less than 1100 units of Hex Nut 17 in stock.'
GO

The following example prints a combination of a local variable, system functions, and a text string using concatenation.

USE AdventureWorks;
GO
DECLARE @MyObject NVARCHAR(257);

SET @MyObject = N'Production.Product';

PRINT N'Object Name: ' + @MyObject
PRINT N'   Object ID: ' + STR(OBJECT_ID(@MyObject))
GO

The following example shows two ways to build a print message by concatenating strings.

-- Build a print message by concatenating strings in a PRINT
-- statement.
PRINT N'The Database Engine instance '
    + RTRIM(@@SERVERNAME)
    + N' is running SQL Server build '
    + RTRIM(CAST(SERVERPROPERTY(N'ProductVersion ') AS NVARCHAR(128)));
GO
-- This shows building a character variable that is used to
-- print a message.
DECLARE @Msg NVARCHAR(300);

SELECT @Msg = N'The Database Engine instance '
    + RTRIM(@@SERVERNAME)
    + N' is running SQL Server build '
    + RTRIM(CAST(SERVERPROPERTY(N'ProductVersion') AS NVARCHAR(128)));

PRINT @Msg;
GO