是否

USE [EPMS]

GO

/****** Object:  StoredProcedure [dbo].[sp_GetCollectionManage]    Script Date: 2016/9/14 10:14:00  ZhangXiaoYong******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[sp_GetCollectionManage]

     @PageSize  INT,--页面大小

     @pageCurrentIndex INT,--当前页数

     @condition nvarchar(MAX),-- 查询条件

     @pageCount INT OUT ,-- 当前页 内容数量

     @orderStr  nvarchar(MAX)  --排序字段

AS

begin

/*获取记录数*/

declare @RecordCount int

/*计算页面数据*/

declare @SqlMain nvarchar(4000)

declare @SqlStr nvarchar(4000)

/* SQL SERVER 拿到查询的数据总数*/

set @SqlMain='select @CountTemp=count(1) from

(

select ef.id, e.dictdata_name, ef.city_id,ef.receive_time,ef.receive_amount,ef.batch_id,ef.city__Manager,ef.contract_id

,ROW_NUMBER()  Over(order by '+@orderStr+' desc) As SerialNumber

from V_city e

left join Collection_Confirmation ef on e.id = ef.city_id where '+@condition+') as T left join Contract fa on T.contract_id = fa.id

left join V_employeeDutyMerger show

on T.dictdata_name=show.cityname and show.dutyname=''城市经理''';

exec sp_executesql @SqlMain,N'@CountTemp int output',@pageCount output; --重要执行

declare @beginIndex as varchar(20)

declare @endIndex as varchar (20)

--定义一个开始数，和一个结束数，用于分页

set @beginIndex=(@pageCurrentIndex-1)*@PageSize+1    set @endIndex=@pageCurrentIndex*@PageSize

/* SQL SERVER 执行查询并分页输出 */

set @SqlStr=

'select T.id, T.dictdata_name, T.receive_time,T.receive_amount,T.batch_id, fa.contract_name,show.name from

(

select ef.id, e.dictdata_name, ef.city_id,ef.receive_time,ef.receive_amount,ef.batch_id,ef.city__Manager,ef.contract_id

,ROW_NUMBER()  Over(order by '+@orderStr+' desc) As SerialNumber

from V_city e

left join Collection_Confirmation ef on e.id = ef.city_id where '+@condition+') as T left join Contract fa on T.contract_id = fa.id

left join V_employeeDutyMerger show

on T.dictdata_name=show.cityname and show.dutyname=''城市经理'' where  T.SerialNumber>='+cast(@beginIndex as varchar(20) )+ ' and T.SerialNumber <='+

cast(@endIndex as varchar(20)) 

--print (@pageCurrentIndex * @PageSize)

--print @SqlCount1

--print @SqlStr

exec (@SqlStr)

end

-- 执行该存储过程

declare @pageCount int;

exec [dbo].[sp_GetCollectionManage] 15,1,' ef.city_id in(210)',@pageCount output,'ef.id'