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

感兴趣的小伙伴，下面一起跟随编程之家 52php.cn的小编两巴掌来看看吧！

代码如下:

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) 

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!