2

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   
1
  • The execution plan should tell you that. I don't know Sybase but it should have something to show that.
    – user1822
    Apr 19, 2012 at 6:41

1 Answer 1

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.