Variable sort order 1

[JohnEck]

I want to code an SP that will have a parameter which allows the user to determine the column the data is sorted by. I tried passing a varchar parameter and using this statement

Order by @SortOrder

and I get error 1008.
"The select item identified by the order by Number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by and expression referencing a cloumn name.

Any help on this would be greatly appreciated.

 

[vidru]

Try using a CASE statement instead:

SELECT T.Field1, T.Field2, T.Field3
  FROM Table T
  WHERE [condition]
  ORDER BY 
    CASE WHEN @SortOrder = 'Field1' THEN T.Field1
         WHEN @SortOrder = 'Field2' THEN T.Field2
         ELSE T.Field3 END

This requires that you know the sort field possibilites beforehand. Otherwise, you're looking at dynamic SQL.

-dave

 

[JohnEck]

Thanks, Very helpful