CODEWITHSUNDEEP
sqlsql-server-2008-r2
Alaa
Alaa

Reputation: 575

case in order by clause

I have this table Users I need to sort select result by parameter, but I face a problem when I sort varchar fields like userName field. how can this be resolved ?

----------------------------------------
| userID | UserName | password |...... |
----------------------------------------
| int    | varchar  | varcahr  | ..... |

    declare @index varchar(100)
    set @index = 2
    select * From users
    order by case @index 
                            when 1 then Number
                            when 2 then CAST(LoginName AS INT)
                            Else Password
                End

Upvotes: 1

Views: 74

Answers (3)

Bhavesh Kashikar
Bhavesh Kashikar

Reputation: 1

declare @pSORT_PARAM VARCHAR(400) -- HERE YOU HAVE TO PASS COLUMN NAME AS PARAMETER

DECLARE @pSORT_ORDER VARCHAR(10) -- HERE YOU HAVE TO PASS SORT ORDER AS ASC OR DESC

DECLARE @pQUERY VARCHAR(400)

SET @pSORT_ORDER=' ASC '

SET @pQUERY='SELECT * FROM USERS'

set @pQUERY =@pQUERY + ISNULL(' ORDER BY '+ @pSORT_PARAM + @pSORT_ORDER,'')

EXEC SP_EXECUTESQL @pQUERY

Upvotes: 0

Raphaël Althaus
Raphaël Althaus

Reputation: 60503

cast number to char, not char to number

case @index
      WHEN 1 THEN RIGHT('0000000000'+ CAST(UserId AS VARCHAR(10)), 10)
      WHEN 2 THEN UserName -- or LoginName, maybe a typo ?
      ELSE Password
      end

Upvotes: 3

podiluska
podiluska

Reputation: 51514

You can't cast most varchars to an int, so you'll have to sort by varchars

Format your order by as

      case @index
      when 1 then RIGHT('000000'+ CONVERT(Varchar(6), UserID)
      when 2 then LoginName
      end

Upvotes: 0

Related Questions