初识 Sql Server存储过程

是否

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'