Posted by dave on 07/06/05 13:54

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]