SQL Server Performance

Order a Select statement by some given variables

Discussion in 'T-SQL Performance Tuning for Developers' started by Cesar, Jan 31, 2005.

  1. Cesar New Member

    Hi,

    I want to order the returned results of this Select statement by the given parameters to make the select, these are @Certificate_Gest0, @Certificate_Gest1, @Certificate_Gest2, @Certificate_Gest3, @Certificate_Gest4, @Certificate_Gest5, @Certificate_Gest6 and @Other_Certificates.


    Here is the Select Statement:


    SET NOCOUNT ON

    SELECT Certificate_gest_id, Certificate_gest_name
    FROM Certificates_gest
    WHERE Certificate_gest_id IN (@Certificate_Gest0, @Certificate_Gest1, @Certificate_Gest2, @Certificate_Gest3, @Certificate_Gest4, @Certificate_Gest5, @Certificate_Gest6,
    (Case When @Other_Certificates IS Null THEN Null ELSE '8' END))

    SET NOCOUNT OFF
    GO


    How can I order the returned fields by these parameters?

    Thanks
  2. mmarovic Active Member

    SELECT Certificate_gest_id, Certificate_gest_name
    FROM Certificates_gest
    WHERE Certificate_gest_id IN (@Certificate_Gest0, @Certificate_Gest1, @Certificate_Gest2, @Certificate_Gest3, @Certificate_Gest4, @Certificate_Gest5, @Certificate_Gest6,
    (Case When @Other_Certificates IS Null THEN Null ELSE '8' END))
    order by case (Cartificate_Gest_ID)
    when @Certificate_Gest0 then 0
    .........
    when @Certificate_Gest6 then 6
    else 7
    end)
  3. Cesar New Member

    That' s fine. It works!

    But I don't understand why this it doesn' work: (The line in bold)



    SELECT Certificate_gest_id, Certificate_gest_name
    FROM Certificates_gest
    WHERE Certificate_gest_id IN (@Certificate_Gest0, @Certificate_Gest1, @Certificate_Gest2, @Certificate_Gest3, @Certificate_Gest4, @Certificate_Gest5, @Certificate_Gest6, (Case When @Other_Certificates IS Null THEN Null ELSE '8' END))

    order by case (Cartificate_Gest_ID)
    when @Certificate_Gest0 then 0
    ...
    when @Certificate_Gest6 then 6
    when @Other_Certificates then 7 end


    And instead, this works:


    SELECT Certificate_gest_id, Certificate_gest_name
    FROM Certificates_gest
    WHERE Certificate_gest_id IN (@Certificate_Gest0, @Certificate_Gest1, @Certificate_Gest2, @Certificate_Gest3, @Certificate_Gest4, @Certificate_Gest5, @Certificate_Gest6, (Case When @Other_Certificates IS Null THEN Null ELSE '8' END))

    order by case (Cartificate_Gest_ID)
    when @Certificate_Gest0 then 0
    ...
    when @Certificate_Gest6 then 6
    else 7 end


    Do you know why?

    Thank you
  4. mmarovic Active Member

    When @Other_Certificates is null
    quote:when @Other_Certificates then 7 end
    means
    if Certificate_gest_id = null then use 7 as case expression value
    Logical expression: expression = null evaluates always to null, not true so in your case expression you don't have branch covering Certificate_gest_id is null condition.
  5. Cesar New Member

    [?] I don' t unerstand it, but it doesn' t matter. thank you very much. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  6. mmarovic Active Member

    Case (Cartificate_Gest_ID)
    ....
    when @Other_Certificates then 7
    end
    doesn't work when @Other_Certificates value is null. In that case code is equivalent to
    Case (Cartificate_Gest_ID)
    ....
    when null then 7
    end
    but value after when is tested if it is equal to expression in case (expression) which means Cartificate_Gest_ID = null has to be true to have 7 as a value of case expression.
    However even when value of Cartificate_Gest_ID is really null, null = null is not considered true in sql.

    SQL boolean logic is 3-valent, logical expression can be true, false and null (unknown). null=null is considered unknown.
  7. Cesar New Member

    The SP in that way it doesn' t work to me when the variable @Other_Certificates Is '8', not when it' s Null, when it's null works. And when @Other_Certificates value is '8', the asp.net web application displays the error:<br /><br />Syntax error when turning the value of varchar 'Some text here..' for a column of data type smallint.<br /><br />The @Other_Certificates variable represents a varchar column, whereas @Certificate_Gest0, @Certificate_Gest1,etc.. represents smallint columns. <br /><br />So, perhaps the only problem is that if I put:<br />when @Other_Certificates then 7 end<br /><br />If @Other_Certificates is equal to a number instead of text, then it works as I tested. But if it' s equal to some text it's then the only case when I receive the error.<br /><br />Correct me please.<br /><br /><br /><br />And I tested another solution which is more approximated to which I want:<br />When 8 Then 7 End<br /><br />Because will be only when @Other_Certificates will return '8' (or when Certificate_gest_id is <img src='/community/emoticons/emotion-11.gif' alt='8)' /> when the order in the list must be 7 (at the end). <br />
  8. mmarovic Active Member

    What is datatype of Certificate_gest_id column?
  9. Cesar New Member

    The column-field Certificate_gest_id is type smallint
  10. mmarovic Active Member

    Then it's clear, you can't compare numeric column to text (if text is not decimal reperesentation of number).

Share This Page