How do I use a parameter in the order by clause?

Hi all,

I have a few asp pages that execute a stored procedure to get information from a database. The information will include stuff like county and route, etc. I want to be able to click on county and sort by it. how can I do this where when I click on county the stored procedure takes the variable assigned to county and uses it in the order by clause in the stored procedure?

Thanks,
Kim

Hey Kim,
This article should help you
http://www.sqlteam.com/item.asp?ItemID=2209
(props to Stumpy on this one)

Now, if you figure out how to order one of the columns in descending order, please let me know!

DECLARE @SortOrder tinyint
SET @SortOrder = 2

SELECT CompanyName,
ContactName,
ContactTitle
FROM Customers
ORDER BY CASE WHEN @SortOrder = 1 THEN CompanyName
WHEN @SortOrder = 2 THEN ContactName
ELSE ContactTitle
END DESC