SQL Server Performance Forum – Threads Archive

Order a Select statement by some given variables

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

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)

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

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.

[?] I don’ t unerstand it, but it doesn’ t matter. thank you very much. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]

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.

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: Syntax error when turning the value of varchar ‘Some text here..’ for a column of data type smallint. The @Other_Certificates variable represents a varchar column, whereas @Certificate_Gest0, @Certificate_Gest1,etc.. represents smallint columns. So, perhaps the only problem is that if I put: when @Other_Certificates then 7 end 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. Correct me please. And I tested another solution which is more approximated to which I want: When 8 Then 7 End 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).

What is datatype of Certificate_gest_id column?

The column-field Certificate_gest_id is type smallint

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

]]>