Can I store SQL Server sort order in a variable?

Posted by Steve Weet on Stack Overflow See other posts from Stack Overflow or by Steve Weet
Published on 2009-04-02T13:44:22Z Indexed on 2012/06/07 10:40 UTC
Read the original article Hit count: 296

Filed under:
|

I have the following SQL within a stored procedure. Is there a way to remove the IF statement and pass the 'ASC'/'DESC' option as a variable?

I know I could do the query a number of different ways, or return a table and sort it externally etc. I would just like to know if I can avoid duplicating the CASE statement.

IF @sortOrder = 'Desc'
  BEGIN
    SELECT * FROM #t_results
    ORDER BY
	CASE WHEN @OrderBy = 'surname'	         THEN surname END DESC,
	CASE WHEN @OrderBy = 'forename'	         THEN forename END DESC,
	CASE WHEN @OrderBy = 'fullName'	         THEN fullName END DESC,
	CASE WHEN @OrderBy = 'userId'	         THEN userId END DESC,
	CASE WHEN @OrderBy = 'MobileNumber'      THEN MSISDN END DESC,
	CASE WHEN @OrderBy = 'DeviceStatus'      THEN DeviceStatus END DESC,
	CASE WHEN @OrderBy = 'LastPosition'      THEN LastPosition END DESC,
	CASE WHEN @OrderBy = 'LastAlert'         THEN LastAlert END DESC,
	CASE WHEN @OrderBy = 'LastCommunication' THEN LastCommunication END DESC,
	CASE WHEN @OrderBy = 'LastPreAlert'      THEN LastPreAlert END DESC	
	END
 ELSE
   BEGIN
    SELECT * FROM #t_results
    ORDER BY
	CASE WHEN @OrderBy = 'surname'	         THEN surname END DESC,
	CASE WHEN @OrderBy = 'forename'	         THEN forename END DESC,
	CASE WHEN @OrderBy = 'fullName'	         THEN fullName END DESC,
	CASE WHEN @OrderBy = 'userId'	         THEN userId END DESC,
	CASE WHEN @OrderBy = 'MobileNumber'      THEN MSISDN END DESC,
	CASE WHEN @OrderBy = 'DeviceStatus'      THEN DeviceStatus END DESC,
	CASE WHEN @OrderBy = 'LastPosition'      THEN LastPosition END DESC,
	CASE WHEN @OrderBy = 'LastAlert'         THEN LastAlert END DESC,
	CASE WHEN @OrderBy = 'LastCommunication' THEN LastCommunication END DESC,
	CASE WHEN @OrderBy = 'LastPreAlert'      THEN LastPreAlert END DESC	
    END
END

© Stack Overflow or respective owner

Related posts about sql

Related posts about sql-server