why does a query with ORDER BY CASE ... fail?

There are two pieces of code.

The first one works fine.
declare @foo varchar(20)

set @foo = 'id'

select top 10 id, name
from sysobjects
order by case
when @foo = 'name' then name
when @foo = 'id' then id
end
The other one fails.
declare @foo varchar(20)

set @foo = 'name'

select top 10 id, name
from sysobjects
order by case
when @foo = 'name' then name
when @foo = 'id' then id
end

-- Server: Msg 245, Level 16, State 1, Line 5
-- Syntax error converting the nvarchar value 'CHECK_CONSTRAINTS' to a column of data type int.
The only difference is the value of @foo variable ('id' or 'name').

Can anyone explain this to me?

--

Rediscover the web
http://www.mozilla.org/firefox/

Try this ...

declare @foo varchar(20), @max INT

SELECT @max = MAX(LEN(id)) FROM sysobjects

set @foo = 'id'
select id, name
from sysobjects
where [name] like 'sys%'
order by case
when @foo = 'name' then name
when @foo = 'id' then REPLICATE('0', @max - LEN(id)) + CAST(id AS VARCHAR(100))
end

Thanks, guys.

Especially for the link, dineshasanka.

Below I pasted modified explanation written by SQL Server MVP Brian Moran.

In this case, SQL Server implicitly converts the data type for the entire CASE expression to the data type in the THEN clause that has the highest order of data-type precedence. In this example, the CASE statement has two possible values that follow a THEN clause: id, which is an integer data type, and name, which is a sysname (nvarchar) data type. The integer data type has a higher precedence than the nvarchar data type, so SQL Server attempts to cast the name expression as an integer if you try to order by that column. Such a conversion isn't allowed, so SQL Server generates the above error.

--

Rediscover the web
http://www.mozilla.org/firefox/

quote:Originally posted by FrankKalis

Noticed the slight variation, Dinesh posted?
He uses two separate CASE expression and therefore doesn't have to deal with data type precedence rules.

Yeah, of course, I did notice it.

That's why his query works, while mine doesn't.

The quote only explains why the original query (mine) fails.

--

Rediscover the web
http://www.mozilla.org/firefox/