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