扩展性很好的sql分页存储过程

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)