分页+多条件查询（时间）存储过程版

if exists(select * from sys.objects where name='[dbo].[Proc_CostMaintenance]')
drop proc [dbo].[Proc_CostMaintenance]--判断数据库中是否存在该存储过程，存在则删除
go
alter proc [dbo].[Proc_CostMaintenance]--创建存储过程
(--参数
@id int,
@gettime nvarchar(32),
@outtime nvarchar(32),
@pageidenx int,
@pagesize int,
@totalcount int output--数据总数
)
as
declare @sqlwhere nvarchar(1024);--条件
declare @pagecount int;--总页数
declare @sql nvarchar(1024);--语句
set @sqlwhere=''
if @id=0
begin
set @sqlwhere=@sqlwhere+N' and Scheduling.STotalCost<0';
end
if @id=1
begin
set @sqlwhere=@sqlwhere+N' and Scheduling.STotalCost>=0';
end
if @gettime<>''
begin
set @sqlwhere=@sqlwhere+N' and CarrierTable.ReceiveDate>='''+CONVERT(nvarchar,@gettime) +'''';
end
if @outtime<>''
begin
set @sqlwhere=@sqlwhere+N' and CarrierTable.ReceiveDate<='''+CONVERT(nvarchar,@outtime) +'''';
end
set @sql=N'select @ROWCOUNT=Count(SchedulingID) from Scheduling,CarrierTable where Scheduling.Isdelete=0 '+@sqlwhere
print @sql
exec sp_executesql @sql,N'@ROWCOUNT int output',@totalcount output;
print @totalcount
if @pageidenx<1
begin
set @pageidenx=1
end
set @pagecount=CEILING(CONVERT(float,@totalcount)/@pagesize);
if @pageidenx>@pagecount
begin
set @pageidenx=@pagecount
end
SET @sql=N'SELECT * FROM
(
select  ROW_NUMBER() OVER(ORDER BY Scheduling.FK_CarriersID) AS RowIndex,Scheduling.FK_CarriersID,u.UserName,CarrierTable.SendCompany,CarrierTable.ReceiveDate,CarrierTable.TotalCost,Scheduling.STotalCost from Scheduling  
inner join [User] as u on u.UserID=Scheduling.FK_UserID
inner join [CarrierTable]  on CarrierTable.CarrierID=Scheduling.FK_CarriersID where Scheduling.Isdelete=0 '+@sqlwhere+'
) temp WHERE temp.RowIndex BETWEEN '+CONVERT(NVARCHAR(32),((@pageidenx-1)*@pagesize+1))+'  AND '+CONVERT(NVARCHAR(32),(@pageidenx*@pagesize))

EXEC sp_executesql @sql--执行存储过程
go