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

USE [LotteryData]
GO
/****** Object:  StoredProcedure [dbo].[sp_GetLotteryOrderByWhere]    Script Date: 01/06/2011 19:20:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER 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*@PageSize

select * 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