What's Wrong With this Code? (#2)

A developer wanted to keep track of some birthdays with the following table design and data.

CREATE TABLE [Birthdays]
(
  [Name] varchar(50),
  [BirthDate] datetime   
)

INSERT INTO [Birthdays] VALUES('Gene Wilder',  '6/11/1935')
INSERT INTO [Birthdays] VALUES('Nicola Tesla', '7/9/1856')
INSERT INTO [Birthdays] VALUES('Miles Davis',  '5/26/1926')

To sort the data by name and by date, the developer wrote a single stored procedure to handle both cases.

CREATE PROC GetBirthdays
  @Ordering int
AS
BEGIN
  SELECT [Name], [BirthDate] FROM [Birthdays]
  ORDER BY
    CASE @Ordering
      WHEN 1 THEN [Birthdate]
      WHEN 2 THEN [Name]
    END
END

The developer tested the proc by passing a value of 1, and was pleased to see a resultset ordered by birth date.

What can go wrong? (Hint: Try passing a 2).

How could the developer fix the problem by changing only 1 line?

posted on Monday, August 07, 2006 10:30 PM by scott

Comments

Monday, August 07, 2006 10:03 PM by Rob Farley

# re: What's Wrong With this Code? (#2)

Scott,

The problem is that [Birthdate] and [Name] are different types.

I'd fix it by altering the ORDER BY clause to:

CASE @Ordering
WHEN 1 THEN [Birthdate]
END,
[Name]

This way, [Birthdate] just gets skipped (well, it uses null) unless @Ordering is 1.

You could do it by converting birthdate to a string using format 112 - but it's horrible to sort on a date that's converted to a string.

Rob
Monday, August 07, 2006 10:24 PM by cadet354

# re: What's Wrong With this Code? (#2)

BOL about CASE returns:
Returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression.

Correct:
CREATE PROC GetBirthdays
@Ordering int
AS
BEGIN
SELECT [Name], [BirthDate] FROM [Birthdays]
ORDER BY
CASE @Ordering
WHEN 1 THEN CAST([Birthdate] AS varchar(50))
WHEN 2 THEN [Name]
END
END
Monday, August 07, 2006 10:31 PM by Ajay Purohit

# re: What's Wrong With this Code? (#2)

The problem was due to DateTime Data Field. As DateTime Data Type has higher precedence in MS-Sql Server 2000, We Need to explicitly cast it.
I have solved the issue and also providing it here.
// Procedure

CREATE PROC GetBirthdays
@Ordering int
AS
BEGIN
SELECT [Name], [BirthDate] FROM [Birthdays]
ORDER BY
CASE @Ordering
WHEN 1 THEN CAST(Birthdate as nvarchar(50))
WHEN 2 THEN Name
END
END

// Execution
Exec GetBirthdays 2
Monday, August 07, 2006 11:10 PM by Glenn Slaven

# re: What's Wrong With this Code? (#2)

Well you could change
WHEN 1 THEN [Birthdate]
to
WHEN 1 THEN cast([Birthdate] as varchar(10))

but that seems like a bit of a hack, I'll think a bit more & see if there's a better solution
Monday, August 07, 2006 11:12 PM by Glenn Slaven

# re: What's Wrong With this Code? (#2)

Sorry, forgot to post what's wrong.

SQl reports
Server: Msg 241, Level 16, State 1, Procedure GetBirthdays, Line 6
Syntax error converting datetime from character string.

because it expects the types returned by the case to be the same as the 1st option.
Monday, August 07, 2006 11:17 PM by Glenn Slaven

# re: What's Wrong With this Code? (#2)

Sorry for the comment spam, it's actually due to Data Type Precedence ( http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_2js5.asp ) The reference for the case statement says in relation to result types:
"Returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression."
Monday, August 07, 2006 11:18 PM by Bryant Likes

# re: What's Wrong With this Code? (#2)


WHEN 1 THEN cast([Birthdate] as varchar(50))

That was pretty tough. I had to look it up on google:
http://www.4guysfromrolla.com/webtech/010704-1.shtml

:)
Monday, August 07, 2006 11:22 PM by Justin King

# re: What's Wrong With this Code? (#2)

Not sure about the 1 line fix? But the problem is due to different types in the CASE statement, and the usual fix is to have multiple case statements.

CASE WHEN @Ordering = 1 THEN [Birthdate] END,
CASE WHEN @Ordering = 2 THEN [Name] END

instead of

CASE @Ordering
WHEN 1 THEN [Birthdate]
WHEN 2 THEN [Name]
END

Tuesday, August 08, 2006 12:14 AM by axms

# re: What's Wrong With this Code? (#2)

CASE @Ordering
WHEN 1 THEN [Birthdate]
ELSE [NAME] ??
Tuesday, August 08, 2006 12:24 AM by Heiko Leuze

# re: What's Wrong With this Code? (#2)

The result type of the CASE statement is the type with the highest precedence from the set of types in result_expressions ... and in this 'case' it's the datetime type.

so i think the solution is to cast the datetime to varchar...


alter PROC GetBirthdays
@Ordering int
AS
BEGIN
SELECT [Name], [BirthDate] FROM [Birthdays]
ORDER BY
CASE @Ordering
WHEN 1 THEN cast([Birthdate] as varchar)
WHEN 2 THEN [Name]
END
END
Tuesday, August 08, 2006 2:13 AM by Dave

# re: What's Wrong With this Code? (#2)

ORDER BY
CASE @Ordering
WHEN 1 THEN cast([Birthdate] as varchar(20))
WHEN 2 THEN [Name]
END

mixing datatypes in dynamic order by doesn't make SQL very happy. : )

CONVERT would also work in this instance:

when 1 then convert(varchar(20), [birthdate])
Tuesday, August 08, 2006 2:36 AM by Erling Paulsen

# re: What's Wrong With this Code? (#2)

SQL Server doesn't like it when you mix data types in sort columns. You need to convert the datetime columns to a properly sortable varchar:

CREATE PROC GetBirthdays
@Ordering int
AS
BEGIN
SELECT [Name],
[BirthDate]
FROM [Birthdays]
ORDER BY
CASE @Ordering
WHEN 1 THEN CONVERT(char(30),[BirthDate],121)
WHEN 2 THEN [Name]
END

END

Tuesday, August 08, 2006 4:28 AM by scott

# re: What's Wrong With this Code? (#2)

Great answers, everyone!

Another solution I've seen is to write the CASE statement as:

CASE @Ordering
WHEN 1 THEN CAST([Birthdate] as sql_variant)
WHEN 2 THEN [Name]
END

Thoughts?
Wednesday, August 09, 2006 3:09 PM by Brandon Kelly

# re: What's Wrong With this Code? (#2)

Let's give this a try:

CREATE PROC GetBirthdays
@Ordering int
AS
BEGIN

SELECT Name, BirthDate
FROM (
SELECT [Name], [BirthDate],
CASE @Ordering
WHEN 1 THEN [Birthdate]
WHEN 2 THEN [Name]
END As SortField
FROM [Birthdays] ) data
ORDER BY data.SortField

END
Thursday, August 10, 2006 1:08 AM by jokiz's blog

# what's wrong with this code series

scottallen has started a "what's wrong with this code" series. some items are trivial and
Monday, August 14, 2006 9:45 PM by Christopher Steen

# Link Listing - August 14, 2006


Announcing
the Windows Mobile Virtual User Group Meeting [Via: trobbins ]
Refactoring
...
Tuesday, August 15, 2006 4:01 PM by Alastair Upton

# re: What's Wrong With this Code? (#2)

Although the solutions converting to varchar allow the stored procedure to run they result in the data being sorted in the wrong order (this is because the dates when converted are in the form 'Jul 9 1856 12:00AM' (depending upon locale). May is chronologicaly earlier than July but will appear after in an alphabetical sort. You can test this by adding the cast/convert statement as an additional column in the result set. sql_variant does achieve the desired result.