扩展性很好的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  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)