stored procs - specifying ORDER BY as a parameter

[dave]

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.

[dave]

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" <us****@polo.devilgas.com> wrote in message
news:%L****************@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.

[AK]

this approach can result in poor performance. just think: even if there
are 2 indexes on postcode and another one on address, the optimizer
cannot use neither index to get ordered results, because it must come
up with one generic plan, and it does not know at compile time how to
order the results.

this might perform better:

if @SortOrder = 'postcode'
then begin
select ...
order by address.postcode
end
else
begin
select ...
order by address
end

Even if don't see any difference when you run it agaist a small test
data set with no indexes, the difference in production could be dramatic

[dave]

"AK" <st***********@gmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

this approach can result in poor performance. just think: even if there
are 2 indexes on postcode and another one on address, the optimizer
cannot use neither index to get ordered results, because it must come
up with one generic plan, and it does not know at compile time how to
order the results.

this might perform better:

if @SortOrder = 'postcode'
then begin
select ...
order by address.postcode
end
else
begin
select ...
order by address
end

Even if don't see any difference when you run it agaist a small test
data set with no indexes, the difference in production could be dramatic

thanks steven (?). makes sense so i'll look at writing that approach into
all of the SP's where i use a non-static ORDER BY.

[AK]

You welcome.
In fact, I'm Alexander. Yesterday I let my son Steven to look up his
e-mail at gmail.com on my PC. Now part of his e-mail is displayed in
Google Groups. How comes