Escape is not working with optional parameters !

in my optional parameter query that uses LIKE operator , Escape special characters is not working . this is part of the query

NAME LIKE  CASE WHEN '%'+@Name+'%' IS NULL THEN NAME ELSE '%'+@Name+'%' END

i thought i can make it like

NAME LIKE CASE WHEN '%'+@Name+'%'ESCAPE '\' IS NULL THEN NAME ELSE '%'+@Name+'%' END

but when i add escape '\' but it raise an error . any help ??

Demo Code :

 set @Name='Restaur\[ant'

      SELECT      [JOB].HOUSE_CODE,  [JOB].JOB_CODE,  [JOB].OPEN_DATE,  [JOB].NAME AS Position,  [JOB].DESCRIPTION,  [RESTAURANT].ZIP_CODE AS ZipCode, 
                           [STATE].STATE_NAME AS State,  [RESTAURANT].CITY_NAME AS City
    FROM          [JOB] INNER JOIN
                           [RESTAURANT] ON  [JOB].HOUSE_CODE =  [RESTAURANT].HOUSE_CODE INNER JOIN
                           [STATE开发者_C百科] ON  [RESTAURANT].STATE_CODE =  [STATE].STATE_CODE INNER JOIN
                           [JOB_CODE] ON  [JOB].HOUSE_CODE =  [JOB_CODE].HOUSE_CODE AND  [JOB].JOB_CODE =  [JOB_CODE].JOB_CODE AND 
                           [RESTAURANT].HOUSE_CODE =  [JOB_CODE].HOUSE_CODE
    WHERE     (  [JOB].NAME LIKE  CASE WHEN '%'+@Name+'%' IS NULL THEN  [JOB].NAME ELSE '%'+@Name+'%' END ESCAPE '\' )

Put the ESCAPE outside of the CASE statement. Also, it's sufficient to just test @Name for NULL.

NAME LIKE CASE WHEN @Name IS NULL THEN NAME ELSE '%'+@Name+'%' END ESCAPE `\` 

EDIT: Here's some demo code to illustrate the technique.

declare @test table (
    name varchar(100)
)

insert into @test
    (name)
    select 'I am 100% confident this works.' union all
    select 'There can be no doubt.' union all
    select 'I would like to eat in fine restaur[ant some day.'

declare @name varchar(100)

/* Case when @name is NULL */
select name
    from @test
    where name like case when @name is null then name else '%'+@name+'%' end escape '\'

set @name = '100\%'

/* Case when @name has a value with a % */
select name
    from @test
    where name like case when @name is null then name else '%'+@name+'%' end escape '\'

set @name = 'restaur\[ant'

/* Case when @name has a value with a [ */
select name
    from @test
    where name like case when @name is null then name else '%'+@name+'%' end escape '\'

Have you tried putting a space before ESCAPE?

+'%'ESCAPE '\' IS

to this:

+'%' ESCAPE '\' IS

What are you looking for it to do? I tried your first example and it behaved the way I would expect it to:

CREATE TABLE test2 (
NAME varchar(100)
)

GO

INSERT test2(name) values('Fred')
INSERT test2(name) values('bert')

GO
DECLARE @name VARCHAR(100)
--SET @name='re'
SELECT * FROM test2
WHERE NAME LIKE CASE WHEN '%'+@Name+'%' IS NULL THEN NAME ELSE '%'+@Name+'%' END

If I supply a value for @Name it only returns names containing that value, if I leave it out it returns all names?

Do you really want to compare '%'+@Name+'%ESCAPE'\' to null? I think you want to put the escape keyword later on, like so:

CASE WHEN '%'+@Name+'%' IS NULL THEN NAME ELSE '%'+@Name+'%' ESCAPE '\' END