CASE Statement

Last Post 17 Jan 2007 01:06 PM by JHunter. 9 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
davdres
New Member
New Member

--
17 Jan 2007 08:56 AM
I had a programmer come to me complaining about a problem he was have with an IF test in a classis ASP page. His complaint was that the IF test never evaluated to true even when the variable contained what he was testing for, but only on the lab server. It worked fine on his development machine. As I questioned him about where the data came from the ultimate source was a CASE function in an T-SQL SELECT statement. Yes, his test machine had a SQL 2005 instance and the lab server had a SQL 2000 instance. Here is an example of what he was doing:

declare @dtout varchar(19)
declare @dtin datetime
select @dtin = ''
--select @dtin = '2007-01-12 23:59:59'
select @dtout = case when @dtin = ''
then ''
else convert(char(19), @dtin, 120)
end
select '|' + @dtout + '|'

The interesting thing to me about this is the change from SQL 2000 to SQL 2005. SQL 2000 looks like it is returning a char(19) type, whereas SQL 2005 seems like it is returning a varchar type. The documentation says that the CASE function returns the highest precedence type from the then and else result expressions. So I guess it comes down to the question what is the type of an empty string? This seems like a bug in SQL 2000 to me. What do you think?
JHunter
New Member
New Member

--
17 Jan 2007 10:27 AM
Just an observation, but, @dtin will never equal '', it is of the type datetime - '' is not a valid datetime. Therefore the else part will always be evaluated...

Jamie
davdres
New Member
New Member

--
17 Jan 2007 11:11 AM
The way it is coded now the else part never gets evaluated because @dtin matches the first condition of the CASE statement. If you comment out the assignment of an empty string to @dtin and uncomment the assignment of a date to @dtin, then the else portion of the CASE statement will be executed.
JHunter
New Member
New Member

--
17 Jan 2007 12:20 PM
Put that down to a really bad typo on my behalf - I obviously meant to say "never" rather than "always" :S as '1900-01-01 00:00:00.000' = '1900-01-01 00:00:00.000' is always true...

Anyway, to explain whats happening...

Basically, char(19) (being, as you say, the datatype lowest precedence) is converted to varchar(19), but, as you'll have ANSI_PADDING set, this will pad the varchar with spaces. If you turn ANSI_PADDING off you should see the expected result.

Jamie
davdres
New Member
New Member

--
17 Jan 2007 12:40 PM
Ahh, I see. However if you test this the ANSI_PADDING option effects the T-SQL above when connected to SQL 2000, but has no effect when connected to SQL 2005.
JHunter
New Member
New Member

--
17 Jan 2007 01:06 PM
I can't repeat that...

I ran:

set ansi_padding off
declare @dtout varchar(19)
declare @dtin datetime
select @dtin = ''
--select @dtin = '2007-01-12 23:59:59'
select @dtout = case when @dtin = ''
then ''
else convert(char(19), @dtin, 120)
end
select '|' + @dtout + '|'

against both an SQL 2000 and SQL 2005 instance, both returned a non padded result - as expected...

Jamie
davdres
New Member
New Member

--
17 Jan 2007 01:22 PM
Hmmm. What do you have for a version on your SQL 2005 instance?
I have 9.00.2047.00, and the set ANSI_PADDING statement has no effect for me on the query.
JHunter
New Member
New Member

--
17 Jan 2007 01:41 PM
Yip, SP1 here too (9.00.2047.00).

Interestingly though, I seem to be getting the result '[' + space + ']', rather than '[' + empty string + ']' (on both SQL 2005 SP1 Developer and SQL 2000 SP3a Standard editions).

Yet SQL 2005 SP1 Express, and SQL 2005 SP1 Standard editions gives the result '[' + empty string + ']' - as expected!

SQL 2000 SP3a Standard, SQL 2005 SP1 Express and SQL 2005 SP1 Standard are all running under Windows 2000 Server SP4. SQL 2005 SP1 Developer is under XP Pro SP2...

hmmm...

Jamie
davdres
New Member
New Member

--
17 Jan 2007 05:15 PM
Yeah, I got the same thing on SQL 2000 Query Analyzer. I added len(@dtout) to the query and it shows 0 (zero) for the length but yet puts a space in the string concatenation. I didn't get this behaviour when running the query from SQL Server Management Studio.

In regard to no padding occuring whether ANSI_PADDING is on or off, could there be another setting affecting the behaviour of ANSI_PADDING on my system?
JHunter
New Member
New Member

--
18 Jan 2007 12:17 AM
Haven't tried QA...as for other settings, a SET statement will override all other server settings.

Jamie
You are not authorized to post a reply.

Acceptable Use Policy