How can I run a query to sort by a column and asc/desc using parameters?

Question

I am working off this example.

http://www.drury.net.nz/2005/04/15/specifying-a-sort-parameter-for-a-tsql-stored-procedure/

CREATE PROCEDURE getEmployees ( @ColumnName varchar(100) ) 
AS 
  SELECT 
    EmployeeID, 
    FirstName, 
    LastName, 
    SSN, 
    Salary 
  FROM 
     Employees 
  ORDER BY 
    CASE 
      WHEN @ColumnName=’LastName’ THEN LastName 
      WHEN @ColumnName=’Salary’ THEN CONVERT(varchar(50), Salary) 
      WHEN @ColumnName=’SSN’ THEN SSN 
    END

The case statement works, but what if I have the following parameters: @SortColumn, @SortDirection.

The @SortColumn could be any column of any type and it seems to use the case statement you have to convert the values to the same type. I suppose I can make them all VARCHAR and just make sure values like DateTime are put in the proper order to sort as I want.

But what if I have the @SortDirection parameter set as ASC or DESC as a VARCHAR value? How can I adjust the query to do change the sort direction?

Answer 1

You can use Dynamic Queries.

For an example, take a look here.

Answer 2

I think your scenary would be like this:

If you have

@SortColumn varchar(50), @SortDirection varchar(50)

then you could do this:

DECLARE @sql nvarchar(4000)    
SET @sql = N'SELECT EmployeeID, FirstName, LastName, SSN, Salary ' +
            'FROM Employees ' +
            'ORDER BY ' + @SortColumn + ' ' + @SortDirection    
EXEC sp_executesql @sql

Of course, you can put inside an stored procedure or whatever you want.

Answer 3

Put the result of select statement for Employee table into a table variable or a temporary table.

Then use an if..else statement to return the result ascending/descending order.

There is nothing wrong with using if..else.

Answer 4

If you don't want to use DSQL case then you can do it like this by having case statements that lead to non filtered parts of the where clause; it wont be quick though. You also need to be careful that the types in your case sections match up.

select * from dbo.Contacts order by case @Sort

When 1 then Surname
When 2 then Forename
When 3 then Telephone_Number
else ''
end asc, 
Case @Sort 
When 4 then Personnel_Ref
When 5 then timesheet_number
When 6 then Telephone_Number
Else ''
End desc

Answer 5

(I don't have enough of a reputation to comment apparently, so I'm replying here)

In reply to:

Do you know how I could avoid using sp_executesql? I have heard that if you use that then it cannot cache the query plan. – Brennan Apr 28 at 0:16

Actually, in most cases, sp_executesql allows the query plan to be cached the same as a stored procedure.

The trick is to use parameterized dynamic sql, like so:

exec sp_executesql N'select from MyTable where myId = @id', N'@id int', @id;

This way, you are running the same query, just substituting in the @id, just like with a stored procedure. With dynamic sql, the query plan is cached based on the string value of the query (the first param to sp_executesql).

The only question I would ask, is why do you have to sort this in the database? I feel like you should be sorting this later on...

However, since the sort expression and direction can NOT be parameterized (they must be concatenated right in to the query string), you will get a separate query plan cached for every sort expression and direction. This probably isn't a big deal.

EDIT: Here's a link explaining how dynamic SQL query plans are cached.

Answer 6

Check the following post http://sivakrishnakuchi.blogspot.com/2009/06/sort-expression-as-parameter-to-stored.html