存储过程

存储过程

这两天一直在做一个组合查询。47个查询条件，多吧，嘿嘿。写了个复杂的存储过程。我自己都看晕了。其实逻辑上还是不难，主要是生成动态的sql 语句。为了纪念这两天的劳动，把存储过程贴到我的blogger上。

CREATE PROCEDURE MySearch_st
@i_Exportname nvarchar(200) ,
@i_Expcode nvarchar(200) ,
@i_Expdesc nvarchar(200) ,
@i_Expdetail nvarchar(4000) ,
@i_Misiccode nvarchar(50) ,
@i_Misicdesc nvarchar(200) ,
@i_Misicdetail nvarchar(4000) ,
@i_Pisiccode nvarchar(50) ,
@i_Pisicdesc nvarchar(200) ,
@i_Pciqcode nvarchar(50) ,
@i_Pciqdesc nvarchar(200) ,
@i_Pclcacode nvarchar(50) ,
@i_Pclcadesc nvarchar(200) ,
@i_Pclcbcode nvarchar(50) ,
@i_Pclcbdesc nvarchar(200) ,
@i_Pclcbdetail nvarchar(4000) ,
@i_Risiccode nvarchar(50) ,
@i_Risicdesc nvarchar(200) ,
@i_Rciqcode nvarchar(50) ,
@i_Rciqdesc nvarchar(200) ,
@i_Rclcacode nvarchar(50) ,
@i_Rclcadesc nvarchar(200) ,
@i_Rclcbcode nvarchar(50) ,
@i_Rclcbdesc nvarchar(200) ,
@i_Rclcbdetail nvarchar(4000) ,
@i_Qtype nvarchar(200) ,
@i_Qinstitution nvarchar(200) ,
@i_Critedeg int ,
@i_Equipdeg int ,
@i_Matdeg int ,
@i_Proddeg int ,
@i_Testdeg int ,
@i_Servedeg int ,
@i_Impdeg int ,
@i_Envadmis int ,
@i_Range int ,
@i_Dealequip int ,
@i_Cortrol int ,
@i_Project int ,
@i_Evaluate int ,
@i_Greenprod int ,
@i_Iso int ,
@i_Tclcbcode nvarchar(50) ,
@i_Tclcbdesc nvarchar(200) ,
@i_Tclcbdetail nvarchar(4000) ,
@i_Equipment nvarchar(200) ,
@i_Manufact nvarchar(200)
AS
SET NOCOUNT ON
declare @i_mysql nvarchar(1000)
set @i_mysql='select customerid from exportmart where 1=1  '
if(@i_Exportname<>'')
set @i_mysql=@i_mysql+' and  expchinese='''+@i_Exportname+''''
if(@i_Expcode<>'')
set @i_mysql=@i_mysql+' and expcode='''+@i_Expcode+''''
if(@i_Expdesc<>'')
set @i_mysql=@i_mysql+' and expdesc='''+@i_Expdesc+''''
if(@i_Expdetail<>'')
set @i_mysql=@i_mysql+' and expdetail='''+@i_Expdetail+''')and customerid in(select customerid from ploytrade where  1=1 '
if @i_Misiccode<>''
set @i_mysql=@i_mysql+' and isiccode='''+@i_Misiccode+''''
if @i_Misicdesc<>''
set @i_mysql=@i_mysql+' and isicdescribe='''+@i_Misicdesc+''''
if @i_Misicdetail<>''
set @i_mysql=@i_mysql+' and detail='''+@i_Misicdetail+''' )and customerid in(select customerid from procotnet where 1=1 '
if @i_Pisiccode<>''
set @i_mysql=@i_mysql+' and isiccode='''+@i_Pisiccode+''''
if @i_Pisicdesc<>''
set @i_mysql=@i_mysql+' and isicdescribe='''+@i_Pisicdesc+''''
if @i_Pciqcode<>''
set @i_mysql=@i_mysql+' and ciqcode='''+@i_Pciqcode+''''
if @i_Pciqdesc<>''
set @i_mysql=@i_mysql+' and ciqdescribe='''+@i_Pciqdesc+''''
if @i_Pclcacode<>''
set @i_mysql=@i_mysql+' and clcacode='+@i_Pclcacode+''''
if @i_Pclcadesc<>''
set @i_mysql=@i_mysql+' and clcadescribe='''+@i_Pclcadesc+''''
if @i_Pclcbcode<>''
set @i_mysql=@i_mysql+' and clcbcode='''+@i_Pclcbcode+''''
if @i_Pclcbdesc<>''
set @i_mysql=@i_mysql+' and clcbdescribe='''+@i_Pclcbdesc+''''
if @i_Pclcbdetail<>''
set @i_mysql=@i_mysql+' and detail='''+@i_Pclcbdetail+''')and customerid in(select customerid from rawmater where 1=1 '
if @i_Risiccode<>''
set @i_mysql=@i_mysql+' and isiccode='''+@i_Risiccode+''''
if @i_Risicdesc<>''
set @i_mysql=@i_mysql+' and isicdescribe='''+@i_Risicdesc+''''
if @i_Rciqcode<>''
set @i_mysql=@i_mysql+' and ciqcode='''+@i_Rciqcode+''''
if @i_Rciqdesc<>''
set @i_mysql=@i_mysql+' and ciqdescribe='''+@i_Rciqdesc+''''
if @i_Rclcacode<>''
set @i_mysql=@i_mysql+' and clcacode='''+@i_Rclcacode +''''
if @i_Rclcadesc<>''
set @i_mysql=@i_mysql+' and clcadecribe='''+@i_Rclcadesc+''''
if @i_Rclcbcode<>''
set @i_mysql=@i_mysql+' and clcbcode='''+@i_Rclcbcode+''''
if @i_Rclcbdesc<>''
set @i_mysql=@i_mysql+' and clcbdescribe='''+@i_Rclcbdesc+''''
if @i_Rclcbdetail<>''
set @i_mysql=@i_mysql+' and detail='''+@i_Rclcbdetail+') and customerid from quality where 1=1'
if @i_Qtype<>''
set @i_mysql=@i_mysql+' and type='''+@i_Qtype+''''
if @i_Qinstitution<>''
set @i_mysql=@i_mysql+' and institution='''+@i_Qinstitution+'''and customerid in(select customerid from proceeding where critedeg='+
@i_Critedeg+' and equipdeg='+@i_Equipdeg+' and matdeg='+@i_Matdeg+' and  proddeg='+@i_Proddeg+
' and testdeg='+@i_Testdeg+' and servedeg='+@i_Servedeg+' and impdeg='+@i_Impdeg+
')and  customerid in(select customerid from envment where envadmis='+@i_Envadmis+
' and dealequip='+@i_Dealequip+' and cortrol='+@i_Cortrol+' and project='+@i_Project+' and evaluate='+@i_Evaluate+
'and greenprod='+@i_Greenprod+' and iso='+@i_Iso+' and range='+@i_Range+
') and customerid in (select customerid from technics'
if @i_Tclcbcode<>''
set @i_mysql=@i_mysql+' where equipcode='''+@i_Tclcbcode+''''
if @i_Tclcbdesc<>''
set @i_mysql=@i_mysql+' and rsvstring1='''+@i_Tclcbdesc+''''
if @i_Tclcbdetail<>''
set @i_mysql=@i_mysql+' and rsvstring2='''+@i_Tclcbdetail+''''

print 'SELECT * FROM CustomerView where customerid in ('+@i_mysql+')'
execute('SELECT * FROM CustomerView where customerid in ('+@i_mysql+')')

RETURN
GO

 

16:16 | 评论 (2)