Where clause short circuit? (sybase)

sybase

Lets say we've created a stored proc that has an optional input parameter.

Will SQL short circuit properly allowing the performance of the following two to be equivalent?
I ask because I am modeling my own proc after another, and it uses the latter method. It would be useful to know whether this was done for a reason or simply because the original author did not think of this. 

select * from
<complex join>
where <various filters>
and (value IS NULL OR myvalue = @value)

OR

if (@value is not null)
begin
    select * from
    <complex join>
    where <various filters>
    AND myvalue = @value
end
else
begin
    select * from
    <complex join>
    where <various filters>
end

Best Answer

Generally, SQL is declarative. That is, you say "what you want" not "how to do it". One consequence of this is that you don't control what order things are processed or evaluated in at the same logical processing step (WHERE clause or GROUP BY clause etc are these steps)

That is, SQL doesn't generally short circuit because the concept doesn't apply.

There are several cases where it does (SQL Server and CASE for example) of course, but in your example you'd need the 2nd option to guarantee it. Note SQL Server has an optimisation for this kind of query but I'd assume Sybase doesn't

Related Question