Can I store SQL Server sort order in a variable?
- by Steve Weet
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
