sqlserver 高效分页的存储过程 基于ROW_NUMBER()

USE [LotteryData]GO/****** Object:  StoredProcedure [dbo].[sp_GetLotteryOrderByWhere]    Script Date: 01/06/2011 19:20:40 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER proc [dbo].[sp_GetLotteryOrderByWhere]@UserName varchar(50),@OrderNo varchar(50),@StartDate varchar(50),@EndDate varchar(50),@ProductID  int ,@LotteryIssueID varchar(20),@Status int,@LotteryStatus int,@PageSize int,@PageIndex int,--从0开始@PageCounts int output,@TotalPayMoney decimal output,@TotalAwardMoney decimal output,@TotalCount int output,@MinAwardMoney decimal,@MaxAwardMoney decimal,@LotteryProviderID int as Begin set nocount on -- select  @TotalCount=COUNT(*),@PageCounts=Count(1),@TotalPayMoney=sum(TotalPrice),@TotalAwardMoney=sum(TotalAwardMoney) from  lotteryOrder lo with(nolock)  inner join LotteryUser..users lu with(nolock) on lo.UserID=lu.UserId where  (lu.UserName = @UserName or @UserName = '') and (@OrderNo = '' or  lo.OrderId like '%'+@OrderNo+'%' or lo.AgentOrderID like '%'+@OrderNo+'%') and lo.CreatedDate between @StartDate and @EndDate  and (@LotteryIssueID = '' or lo.IssueID = @LotteryIssueID)  and (@Status=-1 or  lo.Status=@Status)  and (@LotteryStatus=-1 or lo.LotteryStatus=@LotteryStatus) and (@ProductID=-1 or lo.ProductId=@ProductID) and (@LotteryProviderID=-1 or lo.EngineID=@LotteryProviderID) and ((lo.TotalAwardMoney between @MinAwardMoney and @MaxAwardMoney) or (@MinAwardMoney=0 or @MaxAwardMoney=0))--设置那一行开始DECLARE @start_item AS INTEGER--计算此页中从第几个开始显示set @start_item=@PageIndex*@PageSizeselect * from (   select ROW_NUMBER() over (order by lo.createdDate desc) as item ,lo.*, u.UserName , lo.ProductID as ProductName from     lotteryOrder lo with(nolock)   --inner join LotteryUser..Product lp with(nolock) on lo.ProductId=lp.ProductID   inner join LotteryUser..users u with(nolock) on lo.userID= u.UserId   where    (u.UserName = @UserName or @UserName = '')   and (@OrderNo = '' or lo.OrderId like '%'+@OrderNo+'%' or lo.AgentOrderID like '%'+@OrderNo+'%')   and lo.CreatedDate between @StartDate and @EndDate    and (@LotteryIssueID = '' or lo.IssueID = @LotteryIssueID)   and (@Status=-1 or  lo.Status=@Status)    and (@LotteryStatus=-1 or lo.LotteryStatus=@LotteryStatus)    and (@ProductID=-1 or lo.ProductId=@ProductID)    and (@LotteryProviderID=-1 or lo.EngineID=@LotteryProviderID)   and ((lo.TotalAwardMoney between @MinAwardMoney and @MaxAwardMoney) or (@MinAwardMoney=0 or @MaxAwardMoney=0))   )as T where T.item>=@start_item+1 and T.item<=@start_item+@PageSize end