Sql儲存過程分頁--臨時表儲存

weixin_30924079發表於2020-04-04
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='1' and isnull(strAdditional,'')<>'') or (@strAdd='0' and isnull(strAdditional,'')='') )
        --企業資訊完善度
        and (@Information='' or (@Information='1' and isnull(strSysPerfectRecord,'')<>'') or (@Information='0' and isnull(strSysPerfectRecord,'')='') )
        --企業Logo
        and (@strEntLog='' or (@strEntLog='1' and isnull(strEntLog,'')<>'') or (@strEntLog='0' 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

 

轉載於:https://www.cnblogs.com/elves/p/3522133.html

相關文章