469,167 Members | 1,334 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,167 developers. It's quick & easy.

stored procs - specifying ORDER BY as a parameter

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.
Jul 23 '05 #1
4 8079
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.

Jul 23 '05 #2
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

Jul 23 '05 #3
"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.
Jul 23 '05 #4
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

Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Dave | last post: by
8 posts views Thread by Frank Calahan | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.