全部

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

回复赞