using variable with order by in stored procedure

hi,
i want to use a variable with order by.

for instance
i hava stored procedure like tis:


CREATE PROCEDURE [TEST]
@tip as numeric
AS


declare @no1 numeric
declare @no2 numeric
declare @order char(10)

if @tip=1
set @order='field1'

set @sira=3

select top 1 @no1=field1,@no2=field2 FROM table1 where order by @order


it gives en error means i cant use variable with this syntax?
is it impossible to use variable with order by?or how is the correct syntax?

thanks.

thanks for your answer.
in fact i simplify the stored procedure here.
i will use the same variable with 10 or more queries.so a case constructer will not be sufficient.

i tried the code you send.it is ok with using variable in table name but i can not use this with order by too.

thanks again

Hi ya,

I think that Frank is saying that you can do something like

select top 1 @no1=field1,@no2=field2 FROM table1 where <clauses>
order by case @order when 'field1' then field1 when 'field2' then field2 end

otherwise you'd have to use the dynamic option as per Frank's email


set @order = 'field1'
set @sqlstring = 'select @c = count(*) from table1 order by ' + @order

exec sp_executesql @sqlstring, N'@c INT OUTPUT', @result OUTPUT

Cheers
Twan

hi,thanks.
you are right.
i misunderstood Frank.
thanks a lot.

You could do it with a CASE statement

USE pubs
declare @x int
select @x = 1

select *
from authors
order by
case
when @x = 1 then au_fname
else au_lname
end

thanks everybody.

now i want to order different 'asc' or 'desc'.
for instance:

select *
from authors
order by
case
when @x = 1 then au_fname asc
else au_lname desc
end


it gives syntax error.it needs one asc or desc.

so i tried to do this as below:

select *
from authors
order by
case
when @x = 1 then au_fname else 1/au_lname
end asc

but this times it gives this error:
Arithmetic overflow error converting int to data type numeric.


how can i prevent this error

ok now you're outside the realm of possibility in terms of static SQL. You'd have to either use dynamic SQL, or use the multiple procedure/statement with an if/else to control which will get called (as per Frank's suggestion above)

Cheers
Twan

if you are dealing with numerics, wouldn't - (minus) do the trick?


use pubs
declare @order_column varchar(40),
@order_type varchar(4)

select @order_column = 'price',
@order_type = 'ASC'

select *
from titles
order by
case
when @order_column = 'royalty' then
case
when @order_type = 'DESC' then isnull(-royalty,0)
else royalty
end
when @order_column = 'price' then
case
when @order_type = 'DESC' then isnull(-price,0)
else price
end
end
Bambola

One more point, the data type of the statements in Case statement discussed here should be the same. Can't find a refernece to this but I had read it somewhere.

Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

thaks.
i solved the problem with minus.

i want to ask another question.

i have a query like below:

"select col1,col2 from table1 where col1 in (select col from table2 where col3=1)"

i want to increase the performance.should i use this query like above or
instead i use two different querries lik:

select @col1=col1,col2 from table1
select * from table2 where col=@col1

i mean using one query in another decraeses the performance or not?

It is not quite clear from your example what you're actually trying to retrieve? The two query option is not functionally the same as your first query

Options that you could try

select col1, col2 from table1
where exists (select col from table2 where col3 = 1 and col = table1.col1)

select distinct col1, col2 -- assumes that col1, col2 are distinct in table1
from table1,
table2
where col = col1
and col3 = 1

select col1, col2 from table1, (select col from table2 where col3 = 1) table2
where table1.col1 = table2.col

for the first two options it would perhaps pay to have an index on table2( col, col3 ) if there are lots of rows in table2 and col3=1 also returns lots of rows. If col3=1 returns only a few rows then index table2( col3, col )

Hope this helps
Cheers
Twan

you are right.i wrote the question and querries in a wrong way.

but i got what you mean.with the correct indexing i will get the optimum result with one query.

thanks.