Skip to content
Advertisement

SQL Case Order By specific order and Direction

I have a table that I need sorted on fields By @SortBy and @SortDirection,

for ID,PriorityID,stateType (column type is int) result is OK, but for Title (nvarchar) Query Result is:

Conversion failed when converting the nvarchar value 'Title Column value' to data type int.

Query:

             CASE 
                WHEN @SortDirection = 'ASC' THEN
                 CASE @SortBy
                    WHEN 'ID' THEN ID --int
                    WHEN 'Title' THEN Title  --nvarchar
                    WHEN 'PriorityID' THEN [Priority] --int
                    WHEN 'stateType' THEN [state] --int
                    
                 end
              END ASC
              ,case WHEN @SortDirection = 'DESC' THEN
                CASE @SortBy
                    WHEN 'ID' THEN ID 
                    WHEN 'Title' THEN Title 
                    WHEN 'Priority' THEN [Priority]
                    WHEN 'state' THEN [state]
                END
            END DESC

Advertisement

Answer

The types are different, and that is a problem for the case expressions. The simplest method is a different case for each possibility:

ORDER BY (CASE WHEN @SortDirection = 'ASC' AND @SortBy = 'ID' THEN ID END) ASC,
         (CASE WHEN @SortDirection = 'ASC' AND @SortBy = 'Title' THEN Title END) ASC,
         . . .
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement