Can you parameterize sort order in a sql server prepared statement?

Question

I've got a .Net web system with a VB.Net front end talking to a SQL Server 2005 back end over ADO.Net. Essentially, what I want to do is this:

Dim command As SqlCommand = New SqlCommand("", connection)
command.CommandText = "SELECT * FROM someTable ORDER BY orderValue @SortOrder"
Dim sortParam As SqlParameter = New SqlParameter("@SortOrder", SqlDbType.varChar, 3)
sortParam.Value = "ASC"
command.Parameters.Add(sortParam)
command.Prepare()
reader = command.ExecuteReader()

Where the sort order would be passed in via a query string or some such. This code throws "Incorrect syntax near '@SortOrder'. Statement(s) could not be prepared."

Is this even possible, or do I have some really dumb syntax error I'm not seeing?

(And, yes, the client is only running .net 2.0, so LINQ-based solutions won't work, sadly.

Thanks, all!

Update / Response:

Well, that's what I thought. Thanks for the sanity check, everybody. (For some context, the command string is currently being built dynamically, but we're moving the system in a more prepared statement direction, and this was one of the edge cases I didn't know was possible.

Thanks again!

Answer 1

No, that won't work.

There are two possibilities that I can think of right off the top of my head to do what you're trying to do:

1. Build the SQL string dynamically
2. Return a Data Table from your query, and then use a View to do your sorting.

---

With another 6 years or so of experience (plus SQL Server Versions), I have a way of accomplishing this.

DECLARE @SortByIdASC AS INT;
SET @SortByIdASC = 1;
WITH cte AS (
SELECT Id, Foo, Bar
      , ROW_NUMBER() OVER (ORDER BY Id ASC) AS IdSortAsc
      , ROW_NUMBER() OVER (ORDER BY Id DESC) AS IdSortDesc 
   FROM MyTable
)

SELECT Id, Foo, Bar
   FROM cte 
   ORDER BY CASE WHEN @SortByIdASC = 1 THEN IdSortAsc
                 WHEN @SortByIdASC = 2 THEN IdSortDesc 
                 ELSE ''
                 END 
           , Foo, Bar 

This also allows for Sorting up,down, or even excluding that particular column from the given sort order.

Answer 2

While it is possible to update the columns used in a sort. Take the following example:

declare @fname int,@lname int

select @fname=1,@lname=0

select * from [user]
order by case when @Fname=1 then firstname when @lname=1 then lastname end

I don't think you can use this technique to modify the sort order but you can at least change the columns your sorting on. At least when I try get complaints about syntax with SQL 2005