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 datetime
set @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 money
set @m=92734.9256
print @m

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

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

Result is

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

See what happens when you convert that value into varchar

declare @m money
set @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 xml
set @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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s