楼主就是想要一个查询器,是不:这是俺编的,你参照一下,可能有些哆嗦:-----------------------------------------------------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
exec(@sql)
return
end
(@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
exec(@sql)
return
end
解决方案 »
- 参数类型不正确,或不在可以接受的范围之内,或与其他参数冲突。(我的存储过程有错误了)
- 如何在sql里面查看所有用户的权限!解决就给分!在线等!
- 关于字段为空的比较问题
- 为什么要指定数据库的排序规则
- 这个怎么查的啊 速度帮忙
- 怎么样进入单用户模式与取消单用户模式,服务器有人访问的时候?
- 请教:分组统计时,如何只返回各组的前几行?
- 怎么知道每天数据库都发生些什么变化?
- INNER JOIN 多表聚合联查问题
- SqlServer2000中关于四舍五入函数 Round 的问题?急急急!!!!!
- 臨時表的問題
- 急问 :::: 如果一个表的data(char型)字段是没有数据不是NULL,把它转成NULL 怎么转....???
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
那@sql=select * from tb where a="" and b="2" and c="3"但数据表a字段没有""数据,就会出现一条数据也找不到我看了你的第一种方法,不知道参数怎么传过来,你一开始就设为null了解决加分,再次谢谢
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,'')
...
那@sql=select * from tb where a="" and b="2" and c="3"数据表a字段没有""数据,楼主你仔细看一看 @sql这一句(where a="" and b="2" and c="3")
你就设置条件a="",可是你a字段没有""数据,当然一条数据也没有了..
from tb
where
(a = @a or @a = '' or @a is null)
and
(b = @b or @b = '' or @b is null)