如何把下面的语句转化成where ...case结构,

[brotherfromaq]

declare @test table (context nvarchar(10),type nvarchar(10))
declare @type nvarchar(10)

insert @test values ('1','CRP')
insert @test values ('2','CRP')
insert @test values ('3','CRP')
insert @test values ('4','CRP')
insert @test values ('5','CRP')
insert @test values ('5','CRPT')
insert @test values ('6','CRPT')
insert @test values ('7','CRPT')
insert @test values ('1',NULL)
insert @test values ('1',NULL)
insert @test values ('7','BTO')
insert @test values ('7','BTO')
insert @test values ('7','BTO')
if @type='CRP'
begin
select * from @test where type like '%'+@type+'%' OR type is null
end
else
begin
select * from @test where type like '%'+@type+'%'
end

[zjcxc]

--没有 where case 结构,只有case when

--可以改为:

select * from @test
where (@type='CRP' and (type like '%'+@type+'%' OR type is null))
or (@type<>'CRP' and (type like '%'+@type+'%'))

[vivianfdlpw]

select *
from @test
where type like '%'+@type+'%'
or
nullif(@type,'CRP') is null

[brotherfromaq]

vivianfdlpw:run 出来的结果不对。