一个复杂的存储过程

首先说明一下我这个存储过程的功能：

根据不同的查询条件组合进行查询数据，数据库中有项目信息表Project

有项目区域表ProjectArea

项目信息表Project和项目区域表的关联是通过ProjectArea和AreaID进行一对一关联，项目区域信息中的信息有所属关系，我们可以把它理解成一个树形结构

下面是存储过程对应的查询界面和结果展示界面：查询条件有项目编号、项目名称、业务类型、项目区域、项目起止时间

下面是存储过程

看了之后是否觉得很复杂，这就是我今天写的一个比较难的存储过程

 

------------------------------------------------------------------------------------------------------------------------

-- Function: Select a record from table PropertyT

-- Date Created: 2015年月日

-- Created By:   lijinchang

------------------------------------------------------------------------------------------------------------------------

--这些是查询条件(绿色部分)

ALTER PROCEDURE [dbo].[Lijc_GetProjectInfor]

    @PageSize int,

    @PageIndex int,

    @recordCount int output,

    @ProjectNo varchar(100)=null,

    @ProjectName varchar(100)=null,

    @ProjectArea varchar(100) =NULL,

    @ProjectTimeS varchar(100) =NULL,

    @ProjectTimeE varchar(100) =NULL,

    @BusinessType varchar(100) =NULL,

    @ProjectType varchar(100) =NULL

   

AS

--这里用一个sql判断数据库中临时表是否存在,此临时表用来--存储递归查询项目区域的信息,如果表存在则每查询一次清空表数据,如果表不存在则创建表结构

if not exists(select 1 from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#Area') and type='U')

begin

    create table #Area(AreaID varchar(20),AreaName varchar(200),ParentName varchar(200))

    if @ProjectArea is null or @ProjectArea=''

    with AreaTable(AreaID,AreaName,ParentName) as

      (

       select AreaID,AreaName,ParentName from ProjectArea)

       insert #Area select * from AreaTable

    else

    begin

    with AreaTable(AreaID,AreaName,ParentName) as

      (

       select AreaID,AreaName,ParentName from ProjectArea a where a.AreaID=@ProjectArea

       union all

       select b.AreaID,b.AreaName,b.ParentName from ProjectArea b,AreaTable c where b.ParentID=c.AreaID

      )

      insert #Area select * from AreaTable

      end

end

else

begin

    truncate table #Area

    if @ProjectArea is null or @ProjectArea=''

    begin

    with AreaTable(AreaID,AreaName,ParentName) as

      (

       select AreaID,AreaName,ParentName from ProjectArea)

       insert #Area select * from AreaTable

    end

    else

    begin

    with AreaTable(AreaID,AreaName,ParentName) as

      (

       select AreaID,AreaName,ParentName from ProjectArea a where a.AreaID=@ProjectArea

       union all

       select b.AreaID,b.AreaName,b.ParentName from ProjectArea b,AreaTable c where b.ParentID=c.AreaID

      )

     insert #Area select * from AreaTable

     end

end

--下面这部分是组合查询条件的过程

 DECLARE @sqlfilter  nVARCHAR(max)

 SET @sqlfilter =''

 IF(@ProjectNo IS NOT NULL and @ProjectNo<>'')

 set @sqlfilter=@sqlfilter+' and ProjectNo='''+@ProjectNo+''''

 IF(@ProjectName IS NOT NULL and @ProjectName<>'')

 set @sqlfilter=@sqlfilter+' and ProjectName like ''%'+@ProjectName+'%'''

 IF(@BusinessType IS NOT NULL and @BusinessType<>'')

 set @sqlfilter=@sqlfilter+' and BusinessType = '''+@BusinessType+''''

 IF(@ProjectTimeS IS NOT NULL and @ProjectTimeS<>'')

 set @sqlfilter=@sqlfilter+' and ProjectTime >= '''+@ProjectTimeS+''''

 IF(@ProjectTimeE IS NOT NULL and @ProjectTimeE<>'')

 set @sqlfilter=@sqlfilter+' and ProjectTime <= '''+@ProjectTimeE+''''

 

 

 DECLARE @sqlMain nvarchar(max)--一定要声明为nvarchar类型

 set @sqlMain=''

 set @sqlMain=@sqlMain+'select @recordCount=count(1) from  ProjectArea A ,Project B where B.ProjectArea=A.AreaID and A.AreaID in(

  select areaID from  #Area) ' +@sqlfilter

 --exec sp_executesql @sqlMain N'@recordCount int output'  @recordCount output

--为输出参数赋值

 exec sp_executesql@sqlMain,N'@recordCount int output', @recordCount output   --select @recordCount

--下面就是复杂的查询过程

 set @sqlMain=''

 set @sqlMain='select tb.* from (select A.AreaName,B.*,row_Number() over(order by B.ProjectID) as rowIndex from ProjectArea A ,Project B where B.ProjectArea=A.AreaID and A.AreaID in(

  select areaID from  #Area) '+@sqlfilter+') tb'+' where tb.rowIndex between '+cast((@PageSize*@PageIndex+1) as nvarchar)+' and '+cast(@PageSize*@PageIndex+@PageSize as nvarchar)

 exec(@sqlMain)