SQL Server Performance

using variable with order by in stored procedure

Discussion in 'T-SQL Performance Tuning for Developers' started by oziiii, Jan 8, 2004.

  1. oziiii New Member

    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.
  2. FrankKalis Moderator

    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
  3. FrankKalis Moderator

    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>
  4. oziiii New Member

    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

  5. Twan New Member


    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
  6. oziiii New Member

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

    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
  8. FrankKalis Moderator

  9. bambola New Member

    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

  10. oziiii New Member

    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
  11. Twan New Member


    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
  12. bambola New Member

    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
  13. gaurav_bindlish New Member

    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
  14. oziiii New Member

    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?

  15. Twan New Member


    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
  16. oziiii New Member

    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.

Share This Page