An Overview of the PRINT Statement in SQL Server

The SQL PRINT statement serves to display the user-defined message. For example, you are developing a script with T-SQL Loops, and you want to display some specific message on each iteration of a loop. Then you can use the PRINT statement. Or, you can use it when developing a script with conditional statements. In that case, you ensure that the condition evaluated by the statement performs the correct step or generates the correct output. The PRINT statement can also be used to track the process of a T-SQL query or a stored procedure or to return the customized message.

The current article focuses on the following use cases:

1. Printing a string or int value using the PRINT Statement.
2. Using PRINT in the IF…ELSE statement.
3. Using PRINT in the WHILE Loop.

The syntax of the PRINT statement is the following:

Print string | @variable | str_expression

• string: The value can be a character or Unicode string.
• @variable: The character data type must be character or Unicode character data types.
• str_expression: the value can be an expression return a string. It can be a literal value, string function, and variable.

When we use the PRINT statement to display the specific value, the output returns in the SQL Server Management studio’s messages pane.

Note: The PRINT statement can display 8000 characters long string or 4000 characters long Unicode string. If the length exceeds 8000, the remaining string will be truncated.

Limitations of the PRINT statement

1. The PRINT function returns a character string or UNICODE character string. Hence, while concatenating the string and Integer data types, we must explicitly convert the INT value to char or varchar data type.
2. The SQL Server profiler does not capture PRINT statements.

Example: Print the String value

Suppose you want to write a statement that prints Hello, World to the screen. The T-SQL should be as follows:

Print 'Hello World'

Output:

To print the value stored in the @string variable, we need the following code:

declare @String varchar(30)
set @String='Hello World'
Print @String

Output:

Print an Integer value using the PRINT statement

Use the following T-SQL script:

Print 10

Output:

To print the value of the @intvalue parameter, use the following T-SQL script. The data type of @intvalue is an integer.

declare @IntValue Int
set @IntValue = 10
Print @IntValue

Output:

You can perform basic arithmetic functions using the PRINT statement. Suppose you want to perform the SUM of two values. You can do it by adding the plus (+) sign between two numeric values:

Print 10+10

Output:

Print custom messages using the PRINT statement

Suppose you want to print the current time using the PRINT statement. To do that, we are declaring two SQL PRINT variables named @inputstring and @currentdate. The data type of the @inputstring is varchar(50), and @currentdate is datetime. The query is the following:

declare @inputstring varchar(500)
declare @currentdate datetime
set @inputstring ='The time is '
set @currentdate= (select getdate())
Print @inputstring + @currentdate

Output:

An error occurs. To fix this error, we must explicitly convert the value stored in the @currentdate variable. The T-SQL script should be as follows:

declare @inputstring varchar(500)
declare @currentdate datetime
set @inputstring ='The time is '
set @currentdate= (select getdate())
Print @inputstring + convert(varchar,@currentdate,103)

Output:

Using the PRINT Statement in the IF…ELSE statement

Now, let us print Hello World based on the condition specified in the IF…ELSE loop. If the condition evaluates TRUE, then print Hello World. If it evaluates FALSE, then print Hello India.

if (0=0)
print 'Hello World'
else 
Print 'Hello India'

Output:

Similarly, you can use the values stored in a T-SQL variable to compare and print the output.

I have created the stored procedure that checks the status of the database. If it is online, it prints ONLINE. Else, it prints ERROR. I am passing database_id as an input parameter.

The T-SQL code of the stored procedure is as follows:

create procedure getDBStatus
@DatabaseID int 
as
begin
declare @DBStatus varchar(20)
set @DBStatus=(select state_desc from sys.databases where database_id=@DatabaseID)
if @DBStatus='ONLINE'
Print ' Database is ONLINE'
else
Print 'Database is in ERROR state.'
End

Execute the stored procedure:

Exec getDBStatus 5

Output:

Now, let us test the ELSE condition. Run stored procedure using another value of @databaseID parameter:

use master
go
exec getDBStatus 6

Output:

Using the PRINT Statement in the WHILE loop

Suppose you want to print Hello World 10 times. Then you can use the WHILE loop. Below is the T-SQL code to print hello world multiple times:

Declare @i int =0
declare @iterations int =10
While (@i<@iterations)
Begin
Print 'Hello World'
set @i=@i+1
End

Output:

Now, let us develop the script for the T-SQL query generating the backup of all user databases. To view the script progress, we are using the PRINT statement.

In the script, we’ll use the following var in SQL:

1. @DBcount holds the count of user databases. The data type is an integer.
2. @i holds the incremental values. The data type is an integer.
3. @DBName holds the value of the database name. The data type is varchar(200).
4. @SQLCommand holds the Backup database command. The data type is nvarchar(max).
5. #Databases is a temp table. We are inserting the name of the user databases.

First, the script creates a temp table named #Databases and inserts the user database name into it.

create table #Databases (name varchar(200))
insert into #Databases select name from sys.databases where database_id>4

Then, it populates the count of the user database and saves the value in the @DBCount parameter:

set @DBCount=(select count(1) from #Databases)

Next, the WHILE loop populates the name of the Database. The WHILE loop executes until the values of @i and @DBCount become equivalent.

WHILE (@DBCount>@i)

In the WHILE loop, we use the TOP clause to get the name of the Database from the #Databases table and save it in the @DBName variable.

Begin
set @DBName=(select top 1 name from #Databases)

After that, a dynamic T-SQL command is created. It sets the value of the @DBName parameter in the dynamic SQL.

set @SQLCommand = 'Backup database [' +@DBName+'] to disk =''D:\Backup\' + @DBName +'.bak'''

To verify that the backup database query is correct, we have added the PRINT statement that returns the @SQLCommand variable value.

Print @SQLCommand

The next statement increments the value of @I by one and deletes the record with the name stored in the @DBName variable.

delete from #Databases where name=@DBName
set @I=@i + 1
End

The full script is the following:

set nocount on
declare @DBCount int
declare @i int =0
declare @DBName varchar(200)
declare @SQLCommand nvarchar(max)
create table #Databases (name varchar(200))
insert into #Databases select name from sys.databases where database_id>4 
set @DBCount=(select count(1) from #Databases)
WHILE (@DBCount>@i)
Begin
set @DBName=(select top 1 name from #Databases)
set @SQLCommand = 'Backup database [' +@DBName+'] to disk =''D:\Backup\' + @DBName +'.bak'''
Print @SQLCommand
delete from #Databases where name=@DBName
set @I=@i + 1
End
drop table #Databases

The script output is below:

We can use the above script to prepare the specific script for backing up all user databases.

Summary

The article explained the essence and limitations of the PRINT statement on SQL Server and illustrated its usage by practical examples.

Tags: sql print, sql server, t-sql statements Last modified: August 08, 2022