SQL Server Performance

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

Discussion in 'General Developer Questions' started by chopeen, Apr 28, 2005.

  1. chopeen Member

    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/
  2. dineshasanka Moderator


    declare @foo varchar(20)
    set @foo = 'name'
    select * from sysobjects
    order by case
    when @foo = 'id' then [id] end
    ,case when @foo = 'name' then [name] end

    check
    http://www.windowsitpro.com/Article/ArticleID/26479/26479.html

    quote:Originally posted by chopeen

    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/

  3. Adriaan New Member

    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

  4. chopeen Member

    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/
  5. FrankKalis Moderator

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

    --
    Frank Kalis
    SQL Server MVP
    http://www.insidesql.de
  6. chopeen Member

    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/

Share This Page