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

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

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

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

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.

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."

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

:)

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

CASE @Ordering
WHEN 1 THEN [Birthdate]
ELSE [NAME] ??

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

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])

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

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?

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

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.

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