SQL Server Performance Forum – Threads Archive
using variable with order by in stored procedurehi,
i want to use a variable with order by. for instance
i hava stored procedure like tis:
CREATE PROCEDURE [TEST]
@tip as numeric
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
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
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
create procedure def
as print ‘def’
create procedure ghi
as print ‘ghi’
create procedure entry @whichone int
go IMHO your code would become more modularized and easier to maintain. Frank
Oops, and I forgot to post my standard reference on dynamic SQL.
Please note the potential security issues with this technique Frank
You could do it with a CASE statement USE pubs
declare @x int
select @x = 1 select *
when @x = 1 then au_fname
thanks everybody. now i want to order different ‘asc’ or ‘desc’.
for instance: select *
when @x = 1 then au_fname asc
else au_lname desc
it gives syntax error.it needs one asc or desc. so i tried to do this as below: select *
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
if you are dealing with numerics, wouldn’t – (minus) do the trick?
declare @order_column varchar(40),
@order_type varchar(4) select @order_column = ‘price’,
@order_type = ‘ASC’ select *
when @order_column = ‘royalty’ then
when @order_type = ‘DESC’ then isnull(-royalty,0)
when @order_column = ‘price’ then
when @order_type = ‘DESC’ then isnull(-price,0)
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
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
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
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
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.