<< Windows Workflow Hosting | Home | Dear Candidate >>

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?

Print | posted @ Tuesday, August 08, 2006 2:30 AM

Comments on this entry:

Gravatar # re: What's Wrong With this Code? (#2)
by Rob Farley at 8/8/2006 5:03 AM
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
  
Gravatar # re: What's Wrong With this Code? (#2)
by cadet354 at 8/8/2006 5:24 AM
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
  
Gravatar # re: What's Wrong With this Code? (#2)
by Ajay Purohit at 8/8/2006 5:31 AM
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
  
Gravatar # re: What's Wrong With this Code? (#2)
by Glenn Slaven at 8/8/2006 6:10 AM
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
  
Gravatar # re: What's Wrong With this Code? (#2)
by Glenn Slaven at 8/8/2006 6:12 AM
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.
  
Gravatar # re: What's Wrong With this Code? (#2)
by Glenn Slaven at 8/8/2006 6:17 AM
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."
  
Gravatar # re: What's Wrong With this Code? (#2)
by Bryant Likes at 8/8/2006 6:18 AM

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

:)
  
Gravatar # re: What's Wrong With this Code? (#2)
by Justin King at 8/8/2006 6:22 AM
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

  
Gravatar # re: What's Wrong With this Code? (#2)
by axms at 8/8/2006 7:14 AM
CASE @Ordering
WHEN 1 THEN [Birthdate]
ELSE [NAME] ??
  
Gravatar # re: What's Wrong With this Code? (#2)
by Heiko Leuze at 8/8/2006 7:24 AM
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
  
Gravatar # re: What's Wrong With this Code? (#2)
by Dave at 8/8/2006 9:13 AM
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])
  
Gravatar # re: What's Wrong With this Code? (#2)
by Erling Paulsen at 8/8/2006 9:36 AM
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

  
Gravatar # re: What's Wrong With this Code? (#2)
by scott at 8/8/2006 11:28 AM
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?
  
Gravatar # re: What's Wrong With this Code? (#2)
by Brandon Kelly at 8/9/2006 10:09 PM
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
  
Gravatar # re: What's Wrong With this Code? (#2)
by Alastair Upton at 8/15/2006 11:01 PM
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.
  
Gravatar # re: What's Wrong With this Code? (#2)
by Alistair at 11/2/2010 7:32 AM
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 have been closed on this topic.
Scott Allen
Posts - 869
Comments - 4493
Stories - 14