Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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   
share|improve this question
    
The execution plan should tell you that. I don't know Sybase but it should have something to show that. –  a_horse_with_no_name Apr 19 '12 at 6:41

1 Answer 1

up vote 1 down vote accepted

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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