Pages

Friday, October 2, 2015

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.