Re: Stored procedure and dynamic ORDER BY

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance


I would concur with this method.
I almost ~never sort on in the stored procedure anymore.
I let DotNet (client) handle it.

Also keep in mind that I have thousands of users concurrently hitting my
database.....therefore I try to push everything up to the middle tier
machines.

.............

Of course, you probably won't do that.

Here is an example derived from the hints given at:
http://www.sommarskog.se/dynamic_sql.html


-------------START TSQL
Use Northwind

GO



declare @sortorder varchar(4)

select @sortorder = 'DESC' -- or 'ASC'

declare @col1 varchar(24)

select @col1 = 'EmployeeID' -- Put in one of the 4 columns seen in the CASE
statements below

SELECT *

FROM dbo.Orders

ORDER BY CASE @sortorder

WHEN 'ASC' THEN CASE @col1

WHEN 'OrderID' THEN OrderID

WHEN 'CustomerID' THEN CustomerID

WHEN 'OrderDate' THEN OrderDate

WHEN 'EmployeeID' THEN EmployeeID

END

ELSE NULL

END ASC,

CASE @sortorder

WHEN 'DESC' THEN CASE @col1

WHEN 'OrderID' THEN OrderID

WHEN 'CustomerID' THEN CustomerID

WHEN 'OrderDate' THEN OrderDate

WHEN 'EmployeeID' THEN EmployeeID

END

ELSE NULL

END DESC /*--<<This last DESC is actually missing from Erland's example in
the SELECT * FROM tbl ORDER BY @col area */




"AlanPorter" <AlanPorter@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8517E2FF-8F28-43B9-9CB5-62F6D6DDAEE8@xxxxxxxxxxxxxxxx
Wouldn't it make sense to let the stored proc give you the resultset, and
then you do the sorting on the resultset, possibly on the client side ?

"Jan Nielsen" wrote:

I have a stored procedure which returns a table.
The result is displayed on a web page, and it's now desirable to allow
the
user to sort the table by clicking a column header.

Unfortunately I can't figure out how to use order by dynamically in a
stored
procedure.
I've found a number of discussions on the net, and so far the best result
is
something like this:

CASE WHEN @SortCol1 = 'Name' THEN Name
WHEN @SortCol1 = 'Address' THEN Address
WHEN @SortCol1 = 'Group' THEN Group
ELSE Name
END

This is working, but only ascending. I can't figure out a way to specify
ASC
/ DESC dynamically.
I can specify DESC statically after END. But as soon I try to put it
inside
a CASE or IF sentence I get "Incorrect syntax near the keyword 'DESC'".


Does anyone know a way to do this?

Off course I could just use EXEC, but then I might as well drop the
stored
procedure and just generate and execute the desired SQL string directly
from
the website. Would this really be necessary?

The SQL Server is 2008 SP1 (ent).


TIA,
Jan



.

Relevant Pages

  • Re: random records and paging
    ... RowNum INT NOT NULL IDENTITY, ... CustomerID NCHARNOT NULL, ... EmployeeID INT NOT NULL, ... DECLARE @OrderID AS INT, @OrderDate AS DATETIME, ...
    (microsoft.public.sqlserver.programming)
  • Re: Stored procedure and dynamic ORDER BY
    ... OrderID CustomerID EmployeeID OrderDate RequiredDate ShippedDate ShipVia Freight ShipName ShipAddress ShipCity ShipRegion ShipPostalCode ...
    (microsoft.public.sqlserver.programming)
  • Re: Dynamic Comma delimited output
    ... >>SELECT CustomerID, FIRSTOVER (ORDER BY EmployeeID desc) as R ... And how else would you find the 'first' OrderId based ... (select top 1 OrderID from FirstOddAndEvenOrders as O2 ...
    (microsoft.public.sqlserver.programming)
  • Re: Stored procedure and dynamic ORDER BY
    ... OrderID CustomerID EmployeeID OrderDate RequiredDate ShippedDate ShipVia ...
    (microsoft.public.sqlserver.programming)
  • Re: Stored procedure and dynamic ORDER BY
    ... OrderID CustomerID EmployeeID OrderDate RequiredDate ShippedDate ShipVia Freight ShipName ShipAddress ShipCity ShipRegion ShipPostalCode ...
    (microsoft.public.sqlserver.programming)