SQL Server Performance Forum – Threads Archive
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.
Would this give you some ideas ?
declare @sqlstring nvarchar(1000), @table_name varchar(25)
declare @result int set @table_name = ‘pubs.dbo.authors’
set @sqlstring = ‘select @c = count(*) from ‘ + @table_name exec sp_executesql @sqlstring, N’@c INT OUTPUT’, @result OUTPUT select @result ———–
23 (1 row(s) affected) Frank
http://www.insidesql.de
http://www.familienzirkus.de
Reread it again. You don’t need dynamic SQL at all. A case construct would be sufficient. Sorry, I’m too late for my meeting, surely someone else will reply. [<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]<br /><br />Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /<a target="_blank" href=http://www.familienzirkus.de>http://www.familienzirkus.de</a>
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.
Sorry, I was in a hurry earlier on. I hate those meeting with marketing and sales people. They always make me learn new words (haha). So, getting back to you. IIRC, you have basically the same query multiple time with only changing ORDER by clause, right?
If so, I must say, I don’t like these all-in-one approaches. I mean, a single sp that will handle everything.
Without knowinh what exactly your sp does, I would rather have an entry sp with a single input parameter and execute nested other procedures like this
create procedure abc
as
print ‘abc’
go
create procedure def
as print ‘def’
go
create procedure ghi
as print ‘ghi’
go
create procedure entry @whichone int
as
if @whichone=1
begin
exec abc
end
else
if @whichone=2
begin
exec def
end
else
if @whichone=3
begin
exec ghi
end
go IMHO your code would become more modularized and easier to maintain. Frank
http://www.insidesql.de
http://www.familienzirkus.de
Oops, and I forgot to post my standard reference on dynamic SQL.
http://www.algonet.se/~sommar/dynamic_sql.html
Please note the potential security issues with this technique Frank
http://www.insidesql.de
http://www.familienzirkus.de
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 [email protected] 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.
]]>