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

Tuesday, August 8, 2006

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?


Comments
Rob Farley Tuesday, August 8, 2006
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
cadet354 Tuesday, August 8, 2006
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
Ajay Purohit Tuesday, August 8, 2006
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
Glenn Slaven Tuesday, August 8, 2006
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
Glenn Slaven Tuesday, August 8, 2006
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.
Glenn Slaven Tuesday, August 8, 2006
Sorry for the comment spam, it's actually due to Data Type Precedence ( msdn.microsoft.com/.../default.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."
Bryant Likes Tuesday, August 8, 2006

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

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

:)
Justin King Tuesday, August 8, 2006
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

axms Tuesday, August 8, 2006
CASE @Ordering
WHEN 1 THEN [Birthdate]
ELSE [NAME] ??
Heiko Leuze Tuesday, August 8, 2006
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
Dave Tuesday, August 8, 2006
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])
Erling Paulsen Tuesday, August 8, 2006
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

scott Tuesday, August 8, 2006
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?
Brandon Kelly Wednesday, August 9, 2006
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
Alastair Upton Tuesday, August 15, 2006
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.
Alistair Tuesday, November 2, 2010
The real solution is as follows, as Alastair Upton Stated, it sorts on the Text Version of the month, instead we want it to sort on the numerical version, this can be done using convert and passing the required format style, the solution is as follows:

CASE @Ordering
WHEN 1 THEN [Name]
WHEN 2 THEN convert(varchar(20), [birthdate], 21)
END
Comments are now closed.
by K. Scott Allen K.Scott Allen
My Pluralsight Courses
The Podcast!