Posted by dave on 07/06/05 15:48

ok, found the answer....

use a CASE statement on the ORDER BY.....

ORDER BY
CASE @SortOrder
WHEN 'name' THEN upper(last_name)
WHEN 'postcode' THEN address.postcode
WHEN 'address' THEN address
END


"dave" <usenet@polo.devilgas.com> wrote in message
news:%LOye.2115$184.739@newsfe2-win.ntli.net...
> hi all, hope someone can help....
>
> i'm having trouble calling an SP where the ORDER BY operator is specified
as
> a parameter when the SP is called
>
> my SP is.....
>
> CREATE PROCEDURE dbo.sp_CustSearch (@SearchFor VARCHAR(80) , @SortOrder
> VARCHAR(50))
> AS
>
> BEGIN
>
> SELECT first_name, last_name, postcode , address, town
> FROM customer WITH (nolock)
> WHERE (
> UPPER (last_name) LIKE '%' + @SearchFor + '%'
> OR UPPER(address.postcode) = @SearchFor )
> ORDER BY @SortOrder
>
> END
> GO
>
>
> the line causing the problem is ORDER BY @SortOrder
>
> the error i get on checking the syntax is:
> "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 an expression referencing a column name"
>
> anyone know how to solve this? i'm guessing it's something simple.
>
> enclosing @SortOrder in single quotes gives, unsuprisinlgy, unsorted
> results.
> trying ORDER BY '[' + @SortOrder + ']' gives the same error as
> above
>
> the only way i've managed to get it working so far is to dynamically build
> the SQL statement and then execute that at the end. it's a little messy
> which was why i was trying to get the above working.
>
> thanks in advance.
>
>

[Back to original message]