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

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!

From stackoverflow

• I don't think that is possible, only parameter values can be prepared.

• Nope, you'd need to build it into the SQL query each time, or if you have a sub set of sort orders, cache the command object for these and re-use as suites.

  The short ansswer to the question though is "No"

• 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.
  Robert Wagner : Be careful implementing the dynamic SQL strings, SQL injection. Preferred would be sorting in the view or one of the suggestions with the case statements. It might be a tad slower, but often that doesn't matter.

• 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

• You can in a similar manner to an existing post.

  declare @firstSortField int, @secondSortField int
  set @firstSortField = 1
  set @secondSortField = 3
  
  select firstName, lastName, phoneNumber
  from customers
  order by 
      case @firstSortField when 1 then firstName when 2 then lastName when 3 then phoneNumber else null end, 
      case @secondSortField when 1 then firstName when 2 then lastName when 3 then phoneNumber else null end
  

  JoshBerke : Yes but he wants to change sort order not the field;-)

• This does exactly what you asked, and never puts user-entered strings anywhere near the database.

  //get the requested order from the query string
  string sortOrderRequest = request["SortOrder"].ToUpper();
  string sortParam = "";
  if ( sortOrderRequest.Equals("ASC"))
    sortParam = " order by ordervalue ASC ";
  else if (sortOrderRequest.Equals("DESC"))
    sortParam = " order by ordervalue DESC ";
  
  Dim command As SqlCommand = New SqlCommand("", connection)
  command.CommandText = "SELECT * FROM someTable " + sortParam;
  command.Prepare()
  reader = command.ExecuteReader()
  

• Sure can do. Create two aliased columns, SortASC and SortDESC, fill these with whatever you want, and sort on them.

  -- Testcase setup
  DECLARE @OrderASC bit
  SET @OrderASC = 0
  
  -- Statement required
  SELECT
   Users.*,
   CASE @OrderASC WHEN 1 THEN Users.Alias ELSE null END AS _SortASC,
   CASE @OrderASC WHEN 0 THEN Users.Alias ELSE null END AS _SortDESC
  FROM Users
  ORDER BY _SortASC, _SortDESC DESC