I am trying to write a stored procedure that returns a list of object with the sort order and sort direction selected by the user and passed in as sql parameters.
Lets say I have a table of products with the following columns: product_id(int), name(varchar), value(int), created_date(datetime)
and parameters @sortDir and @sortOrder
I want to do something like
if (@sortOrder = 'name' and @sortDir = 'asc')
then order by name asc
if (@sortOrder = 'created_date' and @sortDir = 'asc')
then order by created_date asc
if (@sortOrder = 'name' and @sortDir = 'desc')
then order by name desc
if (@sortOrder = 'created_date' and @sortDir = 'desc')
then order by created_date desc
CASE is an expression that returns a value. It is not for control-of-flow, like
IF. And you can't use
IF within a query.
Unfortunately, there are some limitations with
CASE expressions that make it cumbersome to do what you want. For example, all of the branches in a
CASE expression must return the same type, or be implicitly convertible to the same type. I wouldn't try that with strings and dates. You also can't use
CASE to specify sort direction.
SELECT column_list_please FROM dbo.Product -- dbo prefix please ORDER BY CASE WHEN @sortDir = 'asc' AND @sortOrder = 'name' THEN name END, CASE WHEN @sortDir = 'asc' AND @sortOrder = 'created_date' THEN created_date END, CASE WHEN @sortDir = 'desc' AND @sortOrder = 'name' THEN name END DESC, CASE WHEN @sortDir = 'desc' AND @sortOrder = 'created_date' THEN created_date END DESC;
An arguably easier solution (especially if this gets more complex) is to use dynamic SQL. To thwart SQL injection you can test the values:
IF @sortDir NOT IN ('asc', 'desc') OR @sortOrder NOT IN ('name', 'created_date') BEGIN RAISERROR('Invalid params', 11, 1); RETURN; END DECLARE @sql NVARCHAR(MAX) = N'SELECT column_list_please FROM dbo.Product ORDER BY ' + @sortOrder + ' ' + @sortDir; EXEC sp_executesql @sql;
Another plus for dynamic SQL, in spite of all the fear-mongering that is spread about it: you can get the best plan for each sort variation, instead of one single plan that will optimize to whatever sort variation you happened to use first. It also performed best universally in a recent performance comparison I ran: