SQL Parametrized order by for a custom column name not working-CodePudding

I have a Procedure that sorts data according to provided sorting field and sorting direction. I had to add new sorting order for a field that is a combination of other two columns, but when I try to sort by the new column name I get an error. Is there any workaround for this?

SELECT CONCAT(COALESCE([FirstName]   ' ',''), COALESCE([LastName],'')) as PatientName FROM [dbo].[patient]
order by 
CASE WHEN @SortOrder = 'ASC' THEN 
            CASE @SortField           
                WHEN 'PatientName' THEN PatientName
                WHEN 'OtherExistingColumn' THEN OtherExistingColumn
            END
        END ASC,
        CASE WHEN @SortOrder = 'DESC' THEN 
            CASE @SortField
                WHEN 'PatientName' THEN PatientName
                WHEN 'OtherExistingColumn' THEN OtherExistingColumn                
            END
        END DESC

I know there is a way to order columns according to their select numeration (order by 1), but for a procedure with 60 fields being selected this definitely is not an option.

CodePudding user response：

Try with a subquery:

SELECT *
FROM (SELECT CONCAT(COALESCE([FirstName]   ' ',''), COALESCE([LastName],'')) as PatientName, 0 as OtherExistingColumn FROM [dbo].[patient]) AS Data
order by 
CASE WHEN @SortOrder = 'ASC' THEN 
            CASE @SortField           
                WHEN 'PatientName' THEN PatientName
                WHEN 'OtherExistingColumn' THEN OtherExistingColumn
            END
        END ASC,
        CASE WHEN @SortOrder = 'DESC' THEN 
            CASE @SortField
                WHEN 'PatientName' THEN PatientName
                WHEN 'OtherExistingColumn' THEN OtherExistingColumn                
            END
        END DESC

CodePudding user response：

Remember: CASE expressions really are expressions. They are not like if statements for choosing which code to run. Instead, they always have a single value as the result. Therefore you can't use them to do something like choose between an ASC or DESC branch.

Instead, if you have numeric data you could decide to multiply (or not) by -1:

ORDER BY 
    CASE @SortField           
        WHEN 'PatientName' THEN PatientName
        WHEN 'OtherExistingColumn' THEN OtherExistingColumn
      END * CASE WHEN @SortOrder = 'DESC' THEN -1 ELSE 1 END

If it's not numeric data, you need both expressions in the ORDER BY, where one of them uses the same value for everything (the existing code does this, but less-efficiently):

ORDER BY  
    CASE WHEN @SortOrder <> 'ASC' THEN NULL
         WHEN @SortField = 'PatientName' THEN PatientName
         WHEN @SortField = 'OtherExistingColumn' THEN OtherExistingColumn
       END ASC,
    CASE WHEN @SortOrder <> 'DESC' THEN NULL
         WHEN @SortField = 'PatientName' THEN PatientName
         WHEN @SortField = 'OtherExistingColumn' THEN OtherExistingColumn                
       END DESC

Additionally, because these really are expressions this will only work if the columns in question use compatible types. CASE expressions don't always work based on constant variables. They have to allow for selecting one branch for one row, and a different branch for the next row, so they can fail if the column types don't agree.

Finally, both options can really hurt performance, since the database can no longer predict, for example, that the value will always follow an index on something like the PatientName column. Instead of walking an index to create the result set, it really will need to build and sort the set in memory.

Therefore you're likely to get MUCH better performance handling a dynamic sort like this in the client code or reporting tool.