|
|
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.
Navigation:
[Reply to this message]
|