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_CustSear ch (@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.p ostcode) = @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]

Re: stored procs - specifying ORDER BY as a parameter

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.postcod e
WHEN 'address' THEN address
END


"dave" <usenet@polo.de vilgas.com> wrote in message
news:%LOye.2115 $184.739@newsfe 2-win.ntli.net...[color=blue]
> hi all, hope someone can help....
>
> i'm having trouble calling an SP where the ORDER BY operator is specified[/color]
as[color=blue]
> a parameter when the SP is called
>
> my SP is.....
>
> CREATE PROCEDURE dbo.sp_CustSear ch (@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.p ostcode) = @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[/color]
a[color=blue]
> variable as part of the expression identifying a column position.[/color]
Variables[color=blue]
> 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.
>
>[/color]

[AK]

Re: stored procs - specifying ORDER BY as a parameter

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.postcod e
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]

Re: stored procs - specifying ORDER BY as a parameter

"AK" <steven.kuzine@ gmail.com> wrote in message
news:1120662133 .422618.170300@ g44g2000cwa.goo glegroups.com.. .[color=blue]
> 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.postcod e
> 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
>[/color]

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]

Re: stored procs - specifying ORDER BY as a parameter

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