SQL Parameter Result if Blank Returning all values

I have this code if in my parameters I use like '123' and nothing for the second one then I get every row returned because its matching the wilcards in the address2 with every row since it开发者_如何学Gos blank. Now how do I stop this from happening?

SELECT     AddressID, Address1, Address2
FROM         Address
WHERE     (Address1 LIKE '%' + @add1 + '%') OR
                      (Address2 LIKE '%' + @add2 + '%')

Thank You

You can use nullif. If the parameters to nullif is equal the result will be null and like null will not give any hits.

SELECT AddressID, Address1, Address2
FROM Address
WHERE (Address1 LIKE '%' + nullif(@add1, '') + '%') OR
      (Address2 LIKE '%' + nullif(@add2, '') + '%')

Empty string will cause the results to come back, but null will not.

if ( @add2 = '' )
begin
    set @add2 = null
end

SELECT     AddressID, Address1, Address2
FROM         Address
WHERE     (Address1 LIKE '%' + @add1 + '%') OR
                      (Address2 LIKE '%' + @add2 + '%')

When @add2 is blank, it will be a positive match for everything in Address2.

Try this:

SELECT AddressID, Address1, Address2
FROM Address
WHERE ((@add1 <> '' and Address1 LIKE '%' + @add1 + '%') 
        OR (@add2 <> '' and Address2 LIKE '%' + @add2 + '%'))

You can use a REPLACE function on top of '%' + @add2 + '%' (and for @add1 probably) to replace %% with '' or anything else that will return nothing.

For example:

SELECT     AddressID, Address1, Address2
FROM         Address
WHERE     (Address1 LIKE REPLACE('%' + @add1 + '%','%%', 'VoidInput')) OR
                      (Address2 LIKE REPLACE('%' + @add2 + '%','%%', 'VoidInput'))

This will match against 'VoidInput' for an empty parameter but otherwise match against original %input% string