PRINT statements are crucial in any programming language. They will allow the routine or program that you are running to dump some type of log or information into a console to be viewed and analyzed. PRINTs are valuable to debugging problems within an application code. In this article, we will explore how to effectively use the PRINT statement to help debug complex T-SQL stored procedures and code.
Getting Started with SQL PRINT
The only thing that you need to do to use PRINT is to append the command PRINT before the value which you want to print to the console. In this example, the console will be SQL Server Management Studio, which is the primary development environment most SQL Server developers are familiar with. Inside of SSMS, you can view your PRINT statements on the “Messages” tab after running a query.
Results will show the SQL queried out of your database tables.
Messages will show the PRINT statements
Let’s run the following command in a batch:
--RUN A SELECT TO VIEW THE "RESULTS" TAB
SELECT 1
--RUN PRINT IN LOWERCASE
print 'hello coding sight reader'
--RUN PRINT IN UPPERCASE
PRINT 'HELLO CODING SIGHT READER'
This script makes the following points:
- Those SELECTs and PRINTs can be run together
- The SELECT and PRINT results populate in separate tabs in SSMS
- PRINT statements are not case-sensitive in SQL Server
Simple Examples
Printing from T-SQL Variable
In the below example, the @PRINTVAR variable is set to a string value of “PRINTVAR”. That variable is then printed on two separate lines in the management console by concatenating the values with CHAR(13).
--SETING A VARIABLE AND PRINTING IT
DECLARE @PRINTVAR VARCHAR(500)
SET @PRINTVAR = 'PRINTVAR'
PRINT @PRINTVAR + CHAR(13) + @PRINTVAR --PRINT THE SAME VARIABLE TWICE ON DIFFERENT LINES.
Printing Other Data Types
NVARCHAR is the target data type that will be required when you print to the console in SSMS. So other data types might be explicitly cast to NVARCHAR with either a cast or convert.
DECLARE @XML XML
SET @XML = '<root>
<A>*A*</A>
<B>*B*</B>
<C>*C*</C>
<body>*Body*</body>
</root>'
PRINT CAST(@XML as NVARCHAR(MAX))
Printing a T-SQL Variable in a Loop
In the below example, a variable of type @I will be increased during each iteration of the loop and printed. This is a good example of a PRINT use case to monitor changes within a routine.
--ITERATE IN A WHILE LOOP AND PRINT EACH ITERATION.
DECLARE @I INT = 1
WHILE(@I <= 20)
BEGIN
PRINT @I
SET @I += 1
END
Complex Example
We have covered several simple concepts of how to get started using the PRINT statement in T-SQL. Let’s examine a more robust and complicated example. We will create a stored procedure that will accept two integer values and multiple those two values together. While the answer will be returned on the results, we will also display a multiplication table in the messages with the PRINT statement.
We will also set NOCOUNT on so that we do not get the 1 row affected message in the multiplication table.
CREATE PROC SPX_MULTIPLY_VALUES(@A INT, @B INT)
AS
SET NOCOUNT ON --REMOVE THE 1 ROWS AFFECTED IN MESSAGE
SELECT @A * @B --RETURN ANSWER
--RETURN MULTIPLICATION TABLE
PRINT '1 '+ '2 '+ '3 '+ '4 '+ '5 '+ '6 '+ '7 '+ '8 '+ '9 '+ '10 '
PRINT '2 '+ '4 '+ '6 '+ '8 '+ '10 '+ '12 '+ '14 '+ '16 '+ '18 '+ '20 '
PRINT '3 '+ '6 '+ '9 '+ '12 '+ '15 '+ '18 '+ '21 '+ '24 '+ '27 '+ '30 '
PRINT '4 '+ '8 '+ '12 '+ '16 '+ '20 '+ '24 '+ '28 '+ '32 '+ '36 '+ '40 '
PRINT '5 '+ '10 '+ '15 '+ '20 '+ '25 '+ '30 '+ '35 '+ '40 '+ '45 '+ '50 '
PRINT '6 '+ '12 '+ '18 '+ '24 '+ '30 '+ '36 '+ '42 '+ '48 '+ '54 '+ '60 '
PRINT '7 '+ '14 '+ '21 '+ '28 '+ '35 '+ '42 '+ '49 '+ '56 '+ '63 '+ '70 '
PRINT '8 '+ '16 '+ '24 '+ '32 '+ '40 '+ '48 '+ '56 '+ '64 '+ '72 '+ '80 '
PRINT '9 '+ '18 '+ '27 '+ '36 '+ '45 '+ '54 '+ '63 '+ '72 '+ '81 '+ '90 '
PRINT '10 '+ '20 '+ '30 '+ '40 '+ '50 '+ '60 '+ '70 '+ '80 '+ '90 '+ '100 '
RETURN
--EXECUTE THE PROCEDURE FOR 1 X 8
EXEC dbo.SPX_MULTIPLY_VALUES 1,8
The result of the actual procedure call simply multiplies the two integers together. The PRINT statements also run simultaneously and provide the multiplication table with which we can check the math and indeed 1 x 8 = 8. This is a good example of bringing extra detail into a stored procedure via the print statement.
Use Cases
Additional Logging
The examples here were not very complex, but in the real world, you might run into some complicated logic in stored procedures. The PRINT statement can help narrow in those complexities and provide insight into the variables and happenings inside of them. When in doubt add a PRINT statement, you can comment them out but the insight provided could save you time and effort later.
Summary
We have covered the PRINT statement in T-SQL. It produces messages which can be retrieved in SQL Server Management Studio or returned to a client application that is reading from your SQL Server database. We discussed the approach for printing T-SQL variables as well as other data type considerations. We used the PRINT statement in a loop and a more complicated stored procedure to show the value that PRINTs can provide in a day-to-day operational manner.
Tags: sql print, t-sql, t-sql statements Last modified: October 27, 2022
