存储过程中动态查询问题,该怎样才效率最高?

[半截烟头]

比如有一个表T1,有三个字段:
[ID] bigint,
[F1] varchar(255),
[F2] varchar(255)

创建一个存储过程动态查询,传递三个参数:

create proc P_Select( @ID bigint,@F1 varchar(255),@F2 varchar(255))
as
begin
select *
from T1
where case when not @ID is null then
[id] =@ID
else
0=0
end
and when not @F1 is null then
F1 like '%' + @F1 +'%'
else
0=0
end
and when not @F2 is null then
F2 like '%' + @F2 +'%'
else
0=0
end
end
这样不行,有没有什么比较好的方法?
除了在存储过程里先生成SQL语句然后再用exec执行这种方法之外.

[lwl0606]

create proc P_Select( @ID bigint,@F1 varchar(255),@F2 varchar(255))
as
begin
select *
from T1
where id like isnull(@ID,'%') AND F1 LIKE ISNULL(@F1,'%') and F2 like isnull(@F2,'%')
end

如果不输入参数的化要 是NULL 而不是‘’

[半截烟头]

lwl0606(寒泉) ( ) 信誉：100 Blog 加为好友
create proc P_Select( @ID bigint,@F1 varchar(255),@F2 varchar(255))
as
begin
select *
from T1
where id like isnull(@ID,'%') AND F1 LIKE ISNULL(@F1,'%') and F2 like isnull(@F2,'%')
end

如果不输入参数的化要 是NULL 而不是‘’

我试过种方法,当表中的记录达到上万条时,我在查询分析器里执行的时候,还没有先生成SQL 语句然后再用EXEC执行查询来的快.
不知道老兄试过没有.

[zhuowei]

首先 已经尽量不要去使用动态的拼接查询字符串的方法
这样效率会很低(亲自测试过的).

如果遇到你这样的需要考虑的参数不是特别多,就考虑枚举把
这样可读性更强,程序更稳定,效率也最高
@ID bigint,@F1 varchar(255),@F2 varchar(255))
IF @ID IS NULL AND @F1 IS NULL AND @F2 IS NULL --000
-------
ELSE IF --001

ELSE IF --010

ELSE IF --011

ELSE IF --100

ELSE IF --101

....................

这样罗列下去 就OK了

[小五五]

create proc proc_search
@deptname varchar(20),
@isstart char(6),
@isend char(6),
@percent int
as
select pid,pname,pe_id,pdept,pd_name,pstatus,pend,begintime,endtime,maxfee
from project_view
where (@deptname='无限制' or pd_name=@deptname)
and (@isstart='无限制' or pstatus=@isstart)
and (@isend='无限制' or pend=@isend)
go

不知道楼主说的是不是多条件任意组合查询呢

[lwl0606]

create proc P_Select( @ID bigint,@F1 varchar(255),@F2 varchar(255))
as
begin
declare @sql nvarchar(4000)
set @sql='select * from T1 where'
if @ID is not null
begin
set @sql=@sql + ' ID='+cONVERT(NVARCHAR(10),@ID)+' and '
end
if @F1 is not null
begin
set @sql=@sql + ' F1='''+@F1+''' and '
end
if @F2 is not null
begin
set @sql=@sql + ' F2='''+@F2+''' and '
end
set @sql=@sql+' 1=1'
print @sql
EXEC(@sql)
end

GO

这样试一下 ，把where 写成动态的

[半截烟头]

TO:gaoninggao()
你的方法跟lwl0606(寒泉) 的方法是一样的啊,我试过这样做,但当记录达到上万条时,还没有EXEC SQL语句快

[半截烟头]

谢谢lwl0606(寒泉),
不过我说的用EXEC执行就是你所说的第二种方法.

TO:Andy_Fay()
你的where子句看不明白啊,'无限制'的条件起什么作用呢?对,我的意思就是多条件任意组合查询.

TO:zhuowei(I'm MS MVP)
看来只有采用您的意见了,不过我一般都有十来个参数,好象罗列不过来啊.没有更好的方法吗?

[gaoninggao]

create proc tb_select @id int,@name varchar(10),@addr varchar(20)
as
begin
select * from tb where sid=(case when @id>0 then @id else 0 end) and sname like @name +'%' and saddr like @addr +'%'
end

[半截烟头]

to:zhuowei(I'm MS MVP)
可是如果有10多个参数时，根本罗列不过来啊