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

I've a store开发者_开发百科d 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?

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

Why does it have to be this way? Something like this makes more sense (untested, probably has some bugs/typos)

create procedure match
    @needle varchar(max), @mode int
as
begin
    declare @query varchar(max)
    if @mode = 1 begin
        set @query = 'SELECT * FROM customers WHERE name LIKE ''%' + @needle + '%'''
    end
    else begin
        set @query = 'SELECT * FROM customers WHERE name = ''' + @needle + '''
    end
    exec @query
end

EDIT: Since you wish to avoid altering the prototype, you can simulate the mode parameter, or simply send the string with the %s

create procedure match
    @modeneedle varchar(max)
as
begin
    declare @mode varchar(1)
    declare @needle varchar(max)

    set @mode=substring(@modeneedle,1,1)
    set @needle=substring(@modeneedle,2,len(@modeneedle))

    declare @query varchar(max)
    if @mode = '1' begin
        set @query = 'SELECT * FROM customers WHERE name LIKE ''%' + @needle + '%'''
    end
    else begin
        set @query = 'SELECT * FROM customers WHERE name = ''' + @needle + '''
    end
    exec @query
end

Note: I would never, ever recommend doing this. It's a sign that you're doing it really, really wrong. Having said that, if you really want to do your own SQL injection, and your stored procedure's like this:

set @query = 'SELECT * FROM tmpCustomer WHERE name LIKE ''%' + @needle + '%'''
exec (@query)

Then running your stored procedure like this:

exec match '~~~~THIS WILL NEVER BE IN THE DATABASE~~~~'' OR name = ''testing'' OR ''notapercentagesign'' = '''

...should do an exact match search for 'testing'.

But frankly, if you need to do stuff like this with your own computer system, then you've lost, and here's some evidence: running your stored procedure like this:

exec match '''; DELETE FROM Customer; SELECT * FROM Customer WHERE name LIKE '' '

...will delete all your Customer records :)

Try:

select *
from customers
where case @exact then name = @query else name like '%' + @query + '%' end;