Re: "Order By" Parameter to a Stored Procedure

[Steve Kass]

Alex,

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
>
>

[Alex Maghen]

This is incredibly helpful, thanks. Just one more thing?
What if I want to have one of the CASEs be more than one Order By column,
for example...

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

[Erland Sommarskog]

Alex Maghen (AlexM...@newsgroup.nospam) writes:
> Okay, well actually, I jumped the gun. I seem to be having some strange
> problem. Can you tell me why the following procedure would be returning
> the error...
>
> 'Conversion failed when converting datetime from character string.' no
> matter what I provide as the parameter?
>
> Select * from UsersT
> ORDER BY
> case upper(@OrderBy)
> when 'USERNAME' then Username
> when 'LASTNAME' then LastName
> when 'FIRSTNAME' then FirstName
> when 'ISINTERNAL' then IsInternal
> when 'ACCESSSTRING' then AccessString
> when 'PASSWD' then Passwd
> when 'DEPARTMENT' then Department
> when 'TITLE' then Title
> when 'EADDR' then EAddr
> when 'BIRTHDATE' then BirthDate
> else LastName
> end

The return type from a CASE expression is always the one and the
same, no matter which WHEN branch that is selected. The datatype
is determined by SQL Server's datatype precendence, which says that
when two types meet, the type with lower precendence is converted
to the type with higher precendence.

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 Maghen]

VERY helpful. Thanks! One more little thing. In the syntax you lay out, is it
possible to support some kind of "ELSE" condition?

Alex

[Steve Kass]

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