分頁+多條件查詢（時間）存儲過程版

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