CODEWITHSUNDEEP
sqlsql-servert-sql
Brennan
Brennan

Reputation: 11686

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

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?

Upvotes: 2

Views: 1646

Answers (5)

u07ch
u07ch

Reputation: 13702

If you don't want to use dynamic SQL 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

Upvotes: 4

John Gibb
John Gibb

Reputation: 10763

(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.

Upvotes: 1

dance2die
dance2die

Reputation: 36985

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.

Upvotes: 0

eKek0
eKek0

Reputation: 23329

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.

Upvotes: 0

Fabio Vinicius Binder
Fabio Vinicius Binder

Reputation: 13214

You can use Dynamic Queries.

For an example, take a look here.

Upvotes: 0

Related Questions