左外连接带查询条件时怎么保留null项

[cshoney]

select a1,a2,b1
from A
left outer join B
on A.a3 = B.b3
where b1 like '%' + @p + '%'


其中@p是从页面传递来的参数
现在想实现当@p值是%%时能查到所有数据，包括b1为null的项
当@p有具体内容时，能查到与其模糊匹配的数据，此时不要b1为nell的项
这个查询条件该怎么写？不要在现有的sql文基础上再增加一层（实际数据量比较大，增加一层的开销受不了，太慢）

[cshoney]

初用sql，存储过程不会用，没有去试，见谅
谢谢3楼4楼给我的灵感，问题已解决
谢谢大家啦

[ssxw]

[Quote=引用楼主 cshoney 的帖子:]
select a1,a2,b1
from A
left outer join B
on A.a3 = B.b3
where b1 like '%' + @p + '%'
[/Quote]

select a1,a2,b1
from A
left outer join B
on A.a3 = B.b3
where B1 is not null and b1 like '%' + @p + '%'

[dawugui]

用存储过程.

create proc my_prce @p as varchar(10)
as
begin
  declare @sql as varchar(100)
  if @p is null
     set @sql = select a1,a2,b1 from A left outer join  B on A.a3 = B.b3'
  else
     set @sql = select a1,a2,b1 from A left outer join  B on A.a3 = B.b3 and where b1 like '%''' + @p + '''%''
  exec(@sql)
end
go

[htl258_Tony]

declare @p varchar(2000)
set @p=isnull(@p,'')
select  a1,a2,b1 
from  A 
  left join B 
    on A.a3 = B.b3 
where b1 like '%' + @p + '%' 

[等不到来世]

select            a1,a2,b1 
from              A 
left outer join  B 
on                A.a3 = B.b3 
where            b1 like '%' + @p + '%'  or isnull(@p,'')=''

[playwarcraft]

select            a1,a2,b1 
from              A 
left outer join  B 
on                A.a3 = B.b3 
where           ( b1 like '%' + isnull(@p,'') + '%' ) or (b1 is null and isnull(@p,'') ='')