Hello.

Looking for a smarter way to code the following. I have a stored
procedure I will be passing several variables to. Some times, some of
the fields used in a WHERE clause will not be passed, and I would like
to avoid having to code a bunch of if statements to set the executing
code. For example, below I would only like to execute the LIKE
conditions only when the variable in question is not NULL. I did a
test and if the variable is set to null, obviously the select does not
return what I'm expecting.

if @switch = "B"
SELECT * from ikb where
ikbtitle like @ins1 and
ikbtitle like @ins2 and
ikbtitle not like @ins3 and
ikbbody like @ins1 and
ikbbody like @ins2 and
ikbbody not like @ins3
end


Thanks for any help or information with this.

>> I would only like to execute the LIKE conditions only when the
variable in question is not NULL. I did a test and if the variable is
set to null, obviously the select does not return what I'm expecting.
<<

SELECT *
FROM Foobar
WHERE kbtitle LIKE COALESCE(@ins1, kbtitle)
AND ikbtitle LIKE COALESCE(@ins2, ikbtitle)
AND ikbtitle NOT LIKE COALESCE(@ins3, '')
AND ikbbody LIKE COALESCE(@ins1, ikbbody)
AND ikbbody LIKE COALESCE(@ins2, ikbbody)
AND ikbbody NOT LIKE COALESCE(@ins3,'')

Hi Jason,

Here's one suggestion. Change your params to '%' if they're null.
That way you don't need the IF statement. I would also rewrite the
"not like" clause as it's CPU intensive. - Louis


select @ins1=isnull(@ins1,'%')
select @ins2=isnull(@ins2,'%')
select @ins3=isnull(@ins3,'%')

SELECT * from ikb where
ikbtitle like @ins1 and
ikbtitle like @ins2 and
ikbtitle not like @ins3 and
ikbbody like @ins1 and
ikbbody like @ins2 and
ikbbody not like @ins3