Interesting CASE behavior -- bug?

(SQL Server 2000, SP3a)
Hello all!
I had a situation where I was getting some extraneous spaces in some string
building, and
it took me a while to figure it out. I think I can appreciate *why* it's do
ing this, but
I wish it weren't.
Consider the following:
declare @.CrLf nchar(2) select @.CrLf = nchar(13) + nchar(10)
declare @.String nvarchar(255) select @.String = N''
select @.String = @.String + case when (len(@.String) != 0) then @.CrLf + @.CrLf
else N'' end +
N'Test'
print N'"' + @.String + N'"'
print [master].[dbo].& #91;fn_varbintohexstr](convert(varbinary
(16),
@.String))
The output is:
" Test"
0x20002000200020005400650073007400
It feels like, for some reason, the CASE is evaluating both "sides" of the r
esultant
expression, and determining that it can be coerced to a NCHAR(2). However,
somewhere
along the lines, it gets confused and is introducing 2 bytes for every NCHAR
-- sort of
like accidentally making it a CHAR(4) that then gets promoted to a NCHAR(4)
(as per the
NULL bytes in every other character).
If I change the definition of @.CrLf to be a NVARCHAR(2), then everything wor
ks as
expected.
It's very odd, and certainly smells like a bug to me.
Thoughts?Hi John,
This behaviour isn't a bug. CASE is an expression that returns one datatype
only, and the datatype it returns is determined by looking at the datatypes
of all the possible results of the expression, and then choosing the
appropriate datatype according to the datatype precedence. So what happens
is that your CASE can either return a zero length NCHAR literal (N''), or a
NCHAR(4) (@.CrLf + @.CrLf) so the return type of your CASE expression will be
NCHAR(4), as this has a higher precedence.
--
Jacco Schalkwijk
SQL Server MVP
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OM4e%23b6$DHA.3048@.tk2msftngp13.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I had a situation where I was getting some extraneous spaces in some
string building, and
> it took me a while to figure it out. I think I can appreciate *why* it's
doing this, but
> I wish it weren't.
> Consider the following:
>
> declare @.CrLf nchar(2) select @.CrLf = nchar(13) + nchar(10)
> declare @.String nvarchar(255) select @.String = N''
> select @.String = @.String + case when (len(@.String) != 0) then @.CrLf +
@.CrLf else N'' end +
> N'Test'
> print N'"' + @.String + N'"'
> print [master].[dbo].& #91;fn_varbintohexstr](convert(varbinary
(16)
, @.String))
>
> The output is:
>
> " Test"
> 0x20002000200020005400650073007400
>
> It feels like, for some reason, the CASE is evaluating both "sides" of the
resultant
> expression, and determining that it can be coerced to a NCHAR(2).
However, somewhere
> along the lines, it gets confused and is introducing 2 bytes for every
NCHAR -- sort of
> like accidentally making it a CHAR(4) that then gets promoted to a
NCHAR(4) (as per the
> NULL bytes in every other character).
> If I change the definition of @.CrLf to be a NVARCHAR(2), then everything
works as
> expected.
> It's very odd, and certainly smells like a bug to me.
> Thoughts?
>|||Ah, yes...I think I grok that -- I think that I forgot that I had *two* @.CrL
fs in my first
expression (hence the doubling).
Thanks for your help!
John Peterson
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:uWhzb36$DHA.3352@.TK2MSFTNGP09.phx.gbl...
> Hi John,
> This behaviour isn't a bug. CASE is an expression that returns one datatyp
e
> only, and the datatype it returns is determined by looking at the datatype
s
> of all the possible results of the expression, and then choosing the
> appropriate datatype according to the datatype precedence. So what happens
> is that your CASE can either return a zero length NCHAR literal (N''), or
a
> NCHAR(4) (@.CrLf + @.CrLf) so the return type of your CASE expression will b
e
> NCHAR(4), as this has a higher precedence.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:OM4e%23b6$DHA.3048@.tk2msftngp13.phx.gbl...
> string building, and
> doing this, but
> @.CrLf else N'' end +
> resultant
> However, somewhere
> NCHAR -- sort of
> NCHAR(4) (as per the
> works as
>