这是经常用的一个分页存储过程 希望大家指点不足

复制代码 代码如下:

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)