You can't do this directly, but you can do something like the following:
If the parameter is @ordCol, put this at the end of your SELECT
statement:
ORDER BY
case when @ordCol = 'ThisColumn' then ThisColumn end,
case when @ordCol = 'ThatColumn' then ThatColumn end,
case when @ordCol = 'OtherColumn' then OtherColumn end
See this article for more examples:
http://databases.aspfaq.com/database/how-do-i-use-a-variable-in-an-order-by-clause.html
-- Steve Kass
-- Drew University
-- http://www.stevekass.com
Alex Maghen wrote:
>Can I pass a Parameter to Stored Procedure which will be the ORDER BY column
>to be used in a SELECT? If so, what's the syntax?
>
>Alex
>
>
case when @ordCol = 'ThisColumn' then ThisColumn, THATCOLUMN end,...
If I try to do this, I get a syntax error because of the Comma. Any ideas?
Alex
Judging from the column names, all columns are varchar except IsInternal
and BirthDate of which the latter is datetime. Datetime has higher
precendence than varchar. (See Books Online for full details on datatype
precedence). This means that the data type of the CASE expression is
datetime.
The remedy is two have:
ORDER BY CASE upper(@OrderBy) WHEN 'USERNAME' THEN .... END,
CASE upper(@OrderBy) WHEN 'ISINTERNAL' THEN IsInternal END,
CASE upper(@OrderBy) WHEN 'BirthDate' THEN Birthdate END
If you need to support multiple sort columns, you also need multiple
CASE expressions. CASE in T-SQL is a scalar expression, not a flow-of-
control statement. If you are not well acquainted with it, I encourage
you to read about it in Books Online.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Alex
You can order by some default column by adding it as a final
ORDER BY item.
order by
case when upper(@OrderBy) = 'USERNAME' then Username end,
case when upper(@OrderBy) = 'LASTNAME' then Lastname end,
case when upper(@OrderBy) = 'FIRSTNAME' then Firstname end,
row_ID
You can also put row_ID into the ELSE part of the CASE expression,
but if you do, you will have the same issues about types as with multiple
WHEN sections. When a CASE expression has no ELSE part, the
ELSE part defaults to NULL. See Books Online for the documentation.
SK