Exact match with a stored procedure using a LIKE in T-SQL?

Question

I've a stored procedure (SP) using a LIKE. Let's simplify it to: SELECT * FROM customers WHERE name LIKE '%' + @query + '%'.

I'd like to use it for an (optional) exact match search without altering the stored procedure, but with a tricky parameter ;)

Is there a way to "cancel" the 2 '%' with a clever @query?

Answer 1

Sure, use a variable to hold the two % signs, and set it to an empty string when you want an exact match.

Using the basic answer from Vinko, you could have

create procedure match
    @needle varchar(max), @mode int
as
begin
declare @like char(1);

set @like = case when @like = 1 then '%' else '' end

SELECT * FROM customers WHERE name LIKE (@like + @needle + @like)
end