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
print @date
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
Consider the following example
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.