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

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 @[email protected]+N' and Scheduling.STotalCost<0';

end

if @id=1

begin

set @[email protected]+N' and Scheduling.STotalCost>=0';

end

if @gettime<>''

begin

set @[email protected]+N' and CarrierTable.ReceiveDate>='''+CONVERT(nvarchar,@gettime) +'''';

end

if @outtime<>''

begin

set @[email protected]+N' and CarrierTable.ReceiveDate<='''+CONVERT(nvarchar,@outtime) +'''';

end

set @sql=N'select @ROWCOUNT=Count(SchedulingID) from Scheduling,CarrierTable where Scheduling.Isdelete=0 '[email protected]

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 @[email protected]

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 '[email protected]+'

) temp WHERE temp.RowIndex BETWEEN '+CONVERT(NVARCHAR(32),((@pageidenx-1)*@pagesize+1))+'  AND '+CONVERT(NVARCHAR(32),(@pageidenx*@pagesize))

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

go