ms sql use like statement result in if statement

Question

declare @d varchar
set @d = 'No filter'

if (@d like 'No filter')
  BEGIN
    select 'matched'
  end
else
  begin
    select 'not matched'
  end

the result of above is always not matched can anybody tell me why and how can I use the like or '=' result in my stored procedure. thanks

Answer 1

You need to declare d as varchar(100) not just varchar, otherwise it just becomes N

declare @d varchar
set @d = 'No filter'

Should be:

declare @d varchar(100)
set @d = 'No filter'

Also no need to use LIKE with this you can use =.

Answer 2

Change your declaration to

declare @d varchar(10)

Then

declare @d varchar(10)

set @d = 'No filter' 

if (@d LIKE 'No filter') 
  BEGIN 
    select 'matched' 
  end 
else 
  begin 
    select 'not matched' 
  end

will work.

Please remember that LIKE is used for pattern matching,

something like

DECLARE @Val VARCHAR(10)
SET @Val = 'foo bar'
if (@Val LIKE '%foo%') 
  BEGIN 
    select 'matched' 
  end 
else 
  begin 
    select 'not matched' 
  end

So in your case you might want to change the code to

declare @d varchar(10)
set @d = 'No filter' 

if (@d = 'No filter') 
  BEGIN 
    select 'matched' 
  end 
else 
  begin 
    select 'not matched' 
  end

with an "="