Stored procedure where column to ORDER BY depends on input parameters

Question

I'm getting three inputs and based on those three inputs, I'm returning. Input items may vary, so I used CASE WHEN statements here.

Is this code correct? If so, how can I make it more optimized?

ALTER Procedure [dbo].[WP_GetAllItems] 
 @IsActive bit,
 @OrderMode bit,
 @OrderBy varchar(75)
as
Begin 
 Select ItemPartNumber, ItemDescription, CreatedDate, InitialPrice from Items where IsActive = @IsActive order by
 CASE WHEN @OrderBy='ItemDescription' AND @OrderMode = 0 THEN ItemDescription END ASC,
 CASE WHEN @OrderBy='ItemDescription' AND @OrderMode = 1 THEN ItemDescription END DESC,
 CASE WHEN @OrderBy='ItemPartNumber' AND @OrderMode = 0 THEN ItemPartNumber END ASC,
 CASE WHEN @OrderBy='ItemPartNumber' AND @OrderMode = 1 THEN ItemPartNumber END DESC,
 CASE WHEN @OrderBy='CreatedDate' AND @OrderMode = 0 THEN CreatedDate END ASC,
 CASE WHEN @OrderBy='CreatedDate' AND @OrderMode = 1 THEN CreatedDate END DESC,
 CASE WHEN @OrderBy='InitialPrice' AND @OrderMode = 0 THEN InitialPrice END ASC,
 CASE WHEN @OrderBy='InitialPrice' AND @OrderMode = 1 THEN InitialPrice END DESC
End

Answer 1

It looks like your code produces the intended results (I assume you've tested it), but not for the right reasons.

You misunderstood the way CASE .. WHEN .. THEN .. END is intended to be used. Check out the official documentation. It has examples. There are supposed to be multiple WHEN .. THEN .. between the CASE and the END.

However, since there is just a single WHEN clause in each CASE clauses, if the WHEN clause is not satisfied, then the whole CASE .. END clause evaluates to NULL. ORDER BY NULL has no effect, so it turns out that only the clauses you want to use are being used. Lucky you.

However, it would improve the code to use CASE in the way it was intended. I can't do it all at once but I can refactor the code slightly.

ALTER Procedure [dbo].[WP_GetAllItems] 
 @IsActive bit,
 @OrderMode bit,
 @OrderBy varchar(75)
as
Begin 
 Select ItemPartNumber, ItemDescription, CreatedDate, InitialPrice from Items where IsActive = @IsActive order by
 IIF( @OrderMode=0,
 CASE
 WHEN @OrderBy='ItemDescription' THEN ItemDescription
 WHEN @OrderBy='ItemPartNumber' THEN ItemPartNumber
 WHEN @OrderBy='CreatedDate' THEN CreatedDate
 WHEN @OrderBy='InitialPrice' THEN InitialPrice
 END, NULL ) ASC,
 IIF( @OrderMode=1,
 CASE
 WHEN @OrderBy='ItemDescription' THEN ItemDescription
 WHEN @OrderBy='ItemPartNumber' THEN ItemPartNumber
 WHEN @OrderBy='CreatedDate' THEN CreatedDate
 WHEN @OrderBy='InitialPrice' THEN InitialPrice
 END, NULL ) DESC
End

• good-to-know: Order by not working in this query.