我现在有四个参数@a,@b,@c,@d,都是字符串
select * from tb
where a=@a and b=@b and c=@c and d=@d
现在有一个要求:当其中任意一个参数@c=""时想让@c不参加查询
也就是where a=@a and b=@b and d=@d
不知道各位理解没?
...全文
11411打赏收藏
求一个存储过程,望高手回答~~~
我现在有四个参数@a,@b,@c,@d,都是字符串 select * from tb where a=@a and b=@b and c=@c and d=@d 现在有一个要求:当其中任意一个参数@c=""时想让@c不参加查询 也就是where a=@a and b=@b and d=@d 不知道各位理解没?
select *
from tb
where
case when isnull(@a,'') = ''
then ''
else a
end like isnull(@a,'')
and
case when isnull(@b,'') = ''
then ''
else b
end like isnull(@b,'')
and
case when isnull(@c,'') = ''
then ''
else c
end like isnull(@c,'')
and
case when isnull(@d,'') = ''
then ''
else d
end like isnull(@d,'')
这样的,是不:
create procedure test( @a varchar(12),@b varchar(12),@c varchar(12),@d varchar(12))
as
begin
declare @sql varchar(3000)
set @sql=''
if @a<>"" or @b<>"" or @c<>""
begin
set @sql='select * from tb where a='''+@a+''' and b='''+@b+''' and d='''+@d+''''
exec(@sql)
end
return
end
create procedure sp_goodsinfo
(@id0 varchar(12)=null,
@name0 varchar(15)=null,
@corptype varchar(15)=null,
@sort varchar(12)=null
)
as
declare @flag int,@sql nvarchar(150)
set @flag=0
set @sql='select * from goods_info'
begin
if (@id0 is not null)
begin
set @sql=@sql+' where goodsid like ''%'+@id0+'%'''
set @flag=@flag+1
end
if (@name0 is not null)
begin
if (@flag=0)
set @sql=@sql+' where goodsname like ''%'+@name0+'%'''
else
set @sql=@sql+' and goodsname like ''%'+@name0+'%'''
set @flag=@flag+1
end
if (@corptype is not null)
begin
if (@flag=0)
set @sql=@sql+' where proid like ''%'+@corptype+'%'''
else
set @sql=@sql+' and proid like ''%'+@corptype+'%'''
set @flag=@flag+1
end
if (@sort is not null)
begin
if (@flag=0)
set @sql=@sql+' where sort='''+@sort+''''
else
set @sql=@sql+' and sort='''+@sort+''''
set @flag=@flag+1
end