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: 317
sql
|sql-server
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