扩展性很好的一个分页存储过程分享

USE [a6756475746]
GO
/****** Object: StoredProcedure [dbo].[tbl_order_SearchWhereAndPage] Script Date: 11/01/2011 09:37:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[tbl_order_SearchWhereAndPage]
@AllCount int OUTPUT,
@PageIndex int,
@PageSize int ,
@minDate datetime,
@maxDate datetime
AS
begin
DECLARE @PageLower int
set @PageLower=@PageSize * @PageIndex
DECLARE @PageUpper int
set @PageUpper= @PageLower + @PageSize - 1

DECLARE @SearchSQL nvarchar(4000)
set @SearchSQL=SELECT * ,( ROW_NUMBER() OVER (ORDER BY [ID] DESC) -1 ) AS RowNumber FROM tbl_order WHERE (1=1)
DECLARE @SearchSQLCount nvarchar(4000)
set @SearchSQLCount=SELECT @count=Count(*) FROM tbl_order WHERE (1=1)
declare @Result [varchar](5000)
set @Result=

if @minDate>convert(datetime,1900-1-2)
begin
set @Result=@Result+ and oDeliveryDate >= +convert(varchar(20),@minDate)+
end
if @maxDate > convert(datetime,1900-1-2)
begin
set @Result=@Result+ and oDeliveryDate <= +convert(varchar(20),dateadd(dd,1,@maxDate))+
end
set @SearchSQLCount=@SearchSQLCount+@Result
set @SearchSQL=@SearchSQL+@Result

SET @SearchSQL = WITH t AS ( + @SearchSQL + )
SELECT * FROM t
WHERE [RowNumber] BETWEEN + convert(varchar(50),@PageLower) + AND + convert(varchar(50),@PageUpper) +
ORDER BY RowNumber

exec (@SearchSQL)

exec sp_executesql @SearchSQLCount ,N@count as int out ,@AllCount out

print @SearchSQL

print @AllCount
end
exec (@Result)