special character in LIKE Operator with COALESCE is not working

Question

I have a Article table as shown below :

create table article(
  artID int,
  arttitle varchar(50)
  )

I have inserted 4 records in as below:

insert into article values (1,'abcd');
insert into article values (2,'asfsdf asdf sdf ');
insert into article values (3,'asdfasdfa sd ');
insert into article values (4,'abcasdfsdd [Little]');

created a test stored procedure:

create procedure test
@aID int = null,
@atit varchar(50) = null
as
select * from article where artID = COALESCE(@aID,artID) and 
arttitle like '%'+COALESCE(@atit,arttitle)+'%';

Here is the problem : When I execute this sp with aid = 1 it results with that record and similarly for aid 2 and 3. But when I execute with aid = 4 no results are coming becuase of those square brackets [Little].

here is the stored procedure execution script :

exec test @aID = 4;

Please help me to achieve this. Thanks! Here is the sqlfiddle link

Answer 1

If you don't want to use any of the function you can directly check is null Check bellow SQL. select * from article where (@aID is null or artID = @aID) and (@atit is null or arttitle like '%'+ (@atit) +'%')