Sql存储过程分页--临时表存储

时间：2023-03-08 19:59:28
Sql存储过程分页--临时表存储 
set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

-- =============================================

-- Author:        hy

-- Create date: <Create Date,,>

-- Description:    后台企业管理

-- [P_V_EffectiveInfo_getTable] '',1,'','','','','','','','','','',-1,'',1,20

-- =============================================

ALTER PROCEDURE [dbo].[P_V_EffectiveInfo_getTable]

@strKeys varchar(300),  ---搜索关键字

@intSerachType int,  ---关键字类型

@intParentIndustry VARCHAR(5), -- 行业大类别

@intIndustry varchar(10), -- 行业类别

@intEffectiveType varchar(2), ---企业性质

@intOverdue varchar(2), ---核实

@dtRegStart varchar(40),--起始日期

@dtRegEnd varchar(40),--dtEndDate

@intMemberLv varchar(2),--会员级别

@intCurrState varchar(2),--状态

@strCrty varchar(30),--单位所在地

@strForm varchar(20), --来源

@intFMdredge INT, -- 是否加入*市场

@strAdd varchar(20),--追加

@Information varchar(100),--信息完善度

@strEntLog varchar(30),--企业Logo

@PageIndex int ,--当前页码

@PageSize int--每页数据条数

AS

BEGIN

      --创建临时表存储数据

        if object_id('tempdb.dbo.#temp999') is not null drop table #temp999 ;

        declare @iEnd int

         declare @iStart int

        ---根据当前页和每页显示的调试获取数据跨度范围

        SET @iStart = (@PageIndex-1)*@PageSize+1

        SET @iEnd = (@PageIndex-1)*@PageSize+@PageSize ;

        ----创建带行号的零时数据插入临时表里面

         with #temp1 as (

             ---普通的查询

        select ROW_NUMBER() over(order by dtRegDate desc) as PageIndex ,strClientID ,strAccount ,strEffectiveName

                ,tb.strName +ISNULL((SELECT strName FROM  zh_Sys_crty AS tc  WHERE tc.intCrtyCode=dbo.SPLIT(ta.intAdderCode,',',1) AND tc.intParentCrtyCode=dbo.SPLIT(ta.intAdderCode,',',0) ),'')  AS ctryNameintAdderCode --省+城市

                ,(SELECT Explainss FROM zh_Sys_Position WHERE dictNO=ta.intIndustry)  AS intIndustrExplainss ,strEffectiveTel ,dtRegDate,CASE WHEN intOverdue=1 THEN '已' ELSE '未' END AS intOverdue  --,MemberLv

                ,intStat,case WHEN ISNULL(strBlImg,'')='' THEN '无' ELSE '有' END AS strBlImg1,strEffectivephone

                --是否追加

                ,case WHEN ISNULL(strAdditional,'')='' THEN '无' ELSE '有' END AS strAdditional

                --获取后台向个人发送信息条数

                ,(SELECT COUNT(*) FROM dbo.zh_Sys_MessageLog tf WHERE ta.strClientID=tf.strClient AND intType=10)AS noteCount

                ,strForm,CASE WHEN ISNULL(strEntLog,'')='' THEN '无' ELSE '有' END AS strEntLog,strSysPerfectRecord

        from  V_EffectiveInfo ta LEFT JOIN  dbo.zh_Sys_crty tb

        ON (dbo.SPLIT(ta.intAdderCode,',',0)=tb.intCrtyCode AND tb.intParentCrtyCode=-1)

        WHERE

        --行业搜索

        (@intParentIndustry='' OR intParentIndustry=@intParentIndustry)

        AND (@intIndustry='' OR intIndustry=@intIndustry)

        AND    (@intEffectiveType='' OR intEffectiveType=@intEffectiveType )

        AND ( @intOverdue='' OR intOverdue=@intOverdue)

        AND (  @dtRegStart='' OR dtRegDate>@dtRegStart  )

        AND (  @dtRegEnd=''  OR dtRegDate<@dtRegEnd )

        AND (@intMemberLv='' OR MemberLv=@intMemberLv )

        AND (@intCurrState='' or intStat=@intCurrState)

        AND ((@strCrty='' or dbo.split(intAdderCode,',',0)+','=@strCrty) or intAdderCode=@strCrty)

        and (@strForm='' or strform=@strForm)

        -- 是否开启加入*市场

        AND (@intFMdredge=-1 OR intFMdredge=@intFMdredge)

        --追加

        and (@strAdd='' or (@strAdd='' and isnull(strAdditional,'')<>'') or (@strAdd='' and isnull(strAdditional,'')='') )

        --企业信息完善度

        and (@Information='' or (@Information='' and isnull(strSysPerfectRecord,'')<>'') or (@Information='' and isnull(strSysPerfectRecord,'')='') )

        --企业Logo

        and (@strEntLog='' or (@strEntLog='' and isnull(strEntLog,'')<>'') or (@strEntLog='' and isnull(strEntLog,'')='') )

        --AND((@intSerachType=1 AND (strAccount=@strKeys OR @strKeys='' ))

        AND((@intSerachType=1 AND (@strKeys='' OR strAccount  like '%'+@strKeys+'%' ))

        OR (@intSerachType=2 AND ( @strKeys='' OR strEffectiveName like '%'+@strKeys+'%'))

        OR (@intSerachType=3 AND ( @strKeys='' OR  strEffectiveTel=@strKeys))

        OR (@intSerachType=4 AND ( @strKeys='' OR  strForm like '%'+@strKeys+'%'))

        OR (@intSerachType=5 AND ( @strKeys='' OR  strClientID like '%'+@strKeys+'%'))

        )

        --连接表，根据ID查询省市中文名

        --AND dbo.SPLIT(ta.intAdderCode,',',1)=tb.intCrtyCode 

        ) select *  into #temp999 from #temp1

         ----查询临时表里面的数据并且输出

         select *  from  #temp999  where PageIndex between CAST(@iStart as varchar) and CAST(@iEnd as varchar)ORDER BY PageIndex asc

          ----- 查询总数据条数

         select COUNT(*) as SunPage  from  #temp999 

         ----查询当前企业对应的招聘条数总数和

         select COUNT(*) as SunJob from zh_u_PositionManage WHERE strClientID in(SELECT strClientID FROM #temp999)

END

相关文章