sql多值查询

zychenzy 2005-07-21 12:51:50
我的查询有4个条件,每个条件又是可有可无的(每次查询可以全选,也可以不选条件),我以前是用IF then 来写的,但是现在条件多了,过多的IF语句会引响查询速度,请问大侠们怎么做,
最好写语句看一下。
...全文
237 点赞 收藏 9
写回复
9 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
qgqchile 2005-07-22
WangZWang(阿来) 的方法 很不错啊,我就是这样做多样查询的,给你个现成的STORE PROCEDURE做参考
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'Pos_ListarPos'
AND type = 'P')
DROP PROCEDURE Pos_ListarPos
GO

CREATE PROCEDURE Pos_ListarPos
@StrXMLDatos VARCHAR(8000) AS

DECLARE @idoc INT


DECLARE @pais NUMERIC
DECLARE @ciudad Numeric


DECLARE @sexo NUMERIC
DECLARE @estadoCivil NUMERIC

DECLARE @licencia NUMERIC
DECLARE @titulo numeric


DECLARE @contador numeric

DECLARE @edad numeric


DECLARE @strSelect VARCHAR(1000)

DECLARE @strFrom VARCHAR(1000)

DECLARE @strWhere VARCHAR(4500)

DECLARE @strOrder VARCHAR(500)

DECLARE @strInsert VARCHAR(1000)

DECLARE @strSQL VARCHAR(8000)

BEGIN TRANSACTION TX

EXEC sp_xml_preparedocument @idoc OUTPUT, @StrXMLDatos


-- Lee documento XML de entrada
SELECT

@pais=pais,
@ciudad=ciudad,


@sexo=sexo,
@estadoCivil=estadoCivil,

@licencia=licencia,
@titulo=titulo,
@edad=edad


FROM OPENXML (@idoc, '/parametros',2)
WITH (

pais NUMERIC,
ciudad numeric,

sexo NUMERIC,
estadoCivil NUMERIC,
nacimiento VARCHAR(10),

licencia NUMERIC,
titulo numeric,
edad numeric
)

EXEC sp_xml_removedocument @idoc



BEGIN



SET @strSelect = ''
SET @strFrom = ''

SET @strSQL = ''
SET @strSelect=''

SET @strWhere=' where id_pos<> '''+ ''''

SET @strSelect ='SELECT a.* '
SET @strFrom = ' from PosI_Ficha a '

if @pais<>0
set @strFrom=@strFrom+' ,admin.dbo.detCodSys b '
set @strWhere =@strWhere+' and pais= '+ cast(@pais as varchar)
+' and '+ cast(@pais as varchar) + ' =b.idCodDet '

--SET @strSQL = @strSelect + @strFrom + @strWhere
--print @strSQL
--print @ciudad

if @ciudad<>0
set @strSelect=@strSelect+' ,c.descCodDet as ciudad '

if @ciudad<>0
set @strFrom=@strFrom+' ,admin.dbo.detCodSys c '

if @ciudad<>0
set @strWhere=@strWhere+' and ciudad= '+cast(@ciudad as varchar)
+' and '+ cast(@ciudad as varchar) + ' =c.idCodDet '


if @sexo<>0
set @strSelect=@strSelect+' ,d.descCodDet as sexo '
set @strFrom=@strFrom+' ,admin.dbo.detCodSys d '
set @strWhere=@strWhere+' and sexo= '+cast(@sexo as varchar)
+' and '+ cast(@sexo as varchar) + ' =d.idCodDet '



if @estadoCivil<>0
set @strSelect=@strSelect+' ,e.descCodDet as estadoCivil '
set @strFrom=@strFrom+' ,admin.dbo.detCodSys e '
set @strWhere=@strWhere+' and estadoCivil= '+cast(@estadoCivil as varchar)
+' and '+ cast(@estadoCivil as varchar) + ' =e.idCodDet '



if @licencia<>0
set @strSelect=@strSelect+' ,g.descCodDet as licencia '
set @strFrom=@strFrom+' ,admin.dbo.detCodSys g '
set @strWhere=@strWhere+' and licencia = '+ cast(@licencia as varchar) +' and '+
cast(@licencia as varchar)+ ' = g.idCodDet'



if @titulo<>0
set @strWhere=@strWhere+' and titulo= '+ cast(@titulo as varchar)

if @edad<>0
set @strWhere=@strWhere+' and DATEDIFF("m",convert(varchar,nacimiento,103),convert (varchar,getdate(),103))/12<= '
+ cast(@edad as varchar)

SET @strSQL = @strSelect + @strFrom + @strWhere

--SELECT a.* ,c.descCodDet as ciudad from PosI_Ficha a ,admin.dbo.detCodSys b ,admin.dbo.detCodSys c where id_pos<> '' and pais= 53 and 53 =b.idCodDet and ciudad= 56 and 56 =c.idCodDet
--SELECT * from PosI_Ficha where id_pos<> '' and DATEDIFF("m",convert(varchar,nacimiento,103),convert (varchar,getdate(),103))/12= 0
END
--print @strSQL
EXEC (@strSQL)

COMMIT TRANSACTION TX
回复
WangZWang 2005-07-21
用动态语句实现,灵活.
回复
zychenzy 2005-07-21
大侠们帮个忙,谢过大家了!
回复
zychenzy 2005-07-21
A B C D四个条件。
if A="" and B<>""AND C<>"" ADN D<>""then sql=......
IF b="" AND ......
这样的话就会有好多种组合,好像是16种,那执行效果不会很好吧,
大侠们怎样处理这种问题的!
回复
WangZWang 2005-07-21
declare @str varchar(8000)
Set @Str='select * from tbl where id=0 '
if ---
set @str=@str+' and ???'
----------- 之类的,这样组成的语句在效率上就有所改观
exec(@str)
回复
seu31199113 2005-07-21
要具体看你是什么条件了!
给一个例子,帮你分析分析
回复
mengzulin 2005-07-21
select * from table
where (id=@id or @id=null) and (name = @name or @name=null) and (age =@age or age<0)
回复
hsj20041004 2005-07-21
还是用IF then 比较好的。
这样好判断,代码也清晰,可读性强。
回复
zychenzy 2005-07-21
写一个让我看一下,先谢了。
回复
相关推荐
发帖
应用实例
创建于2007-09-28

2.7w+

社区成员

MS-SQL Server 应用实例
申请成为版主
帖子事件
创建了帖子
2005-07-21 12:51
社区公告
暂无公告