Understanding PRINT statement

PRINT statement in SQL Server is used to return user-defined message to a client. A Simple usage is

PRINT 'Hello SQL World'
which prints the string ‘Hello SQL World’

However you need to understand that the return type of PRINT statement is varchar or Unicode depends on the input string. Also if you use other values, they will be implicitly converted to character data type

declare @date datetime
set @date='20151019'
print @date

The result is Oct 19 2015 12:00AM. The result is same as explicitly converting it to varchar

declare @date datetimeset @date='20151019'select cast(@date as varchar(30)) as date


The result is

date
----------------------------
Oct 19 2015 12:00AM.
If you use SELECT statement the resultant data type is datetime

declare @date datetime
set @date='20151019' 
select @date as date

The result is

date
-----------------------
2015-10-19 00:00:00.000
Consider the following example

declare @m moneyset 
@m=92734.9256
print @m

Result is 92734.93 (adjusted to 2 decimals). See what happens if you just SELECT it

declare @m moneyset @m=92734.9256select @m as money

Result is

money
---------------------
92734.9256

See what happens when you convert that value into varchar

declare @m moneyset 
@m=92734.9256
select cast(@m as varchar(30)) as money

Result is

money
------------------------------
92734.93
Also a value of datatype that can not be implicitly converted to varchar, cannot be directly used in PRINT statement

declare @t xmlset 
@t ='<a>sql</a>'
print @t
You get the following error

Msg 257, Level 16, State 3, Line 3

Implicit conversion from data type xml to nvarchar is not allowed. Use the CONVERT function to run this query.

If you do explicit conversion it works fine

declare @t xml
set @t ='<a>sql</a>'
print cast(@t as nvarchar(100))

The result is <a>sql</a>

All inputs should be either implicitly converted or explicitly converted to character data types. So beware of these and use PRINT statement accordingly.