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
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。