转<存储过程实现分页2>

USE [xltd_b2b]
GO
/****** 对象:  StoredProcedure [dbo].[Proc_G_SingleTable_Select]    脚本日期: 04/05/2012 14:43:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO













ALTER PROCEDURE  [dbo].[Proc_G_SingleTable_Select]
/*    
***************************************************************    
                   **  大数据量使用  
***************************************************************    
参数说明:    
1.Tables             :表名称,视图    
2.PrimaryKey         :主关键字    
3.Sort               :排序语句，不带Order By 比如：NewsID Desc,OrderRows Asc   
4.CurrentPage        :当前页码    
5.PageSize           :分页尺寸  
6.Fields             :列    
7.Filter             :过滤语句，不带Where     
8.Group             :Group语句,不带Group By   
9.PageCount             :总页数
10.TotalRecord            :总记录
    
***************************************************************/    
(    
@Tables varchar(1000),    
@PrimaryKey varchar(100),    
@Sort varchar(200) = NULL,    
@CurrentPage int = 1,    
@PageSize int = 10,    
@Fields varchar(1000) = '*',    
@Filter varchar(1000) = NULL,    
@Group varchar(1000) = NULL ,  
@PageCount int  = 0 OUTPUT,    
@TotalRecord int = 0  OUTPUT
)    
AS   
SET NOCOUNT ON   

DECLARE @strSortColumn varchar(500)   
DECLARE @SqlCount nvarchar(1000)     
DECLARE @operator char(2)    
DECLARE @type varchar(100)    
DECLARE @prec int   
DECLARE @SortStr   nvarchar(200)

set @strSortColumn = ''

/*设定排序语句.  */
IF @Sort IS NULL OR @Sort = ''   
    SET @Sort = @PrimaryKey   
set @SortStr = @Sort

if charindex(',',@Sort) >0
set @Sort = substring(@Sort,0,charindex(',',@Sort))

set @operator = '>='
IF CHARINDEX('DESC',@Sort)>0    
BEGIN   
set @Sort = REPLACE(@Sort, 'DESC', '')
set @operator = '<='
END   
else
If CHARINDEX('ASC',@Sort)>0    
set @Sort = REPLACE(@Sort, 'ASC', '')

SELECT @type=t.name, @prec=c.prec    
FROM sysobjects o     
JOIN syscolumns c on o.id=c.id    
JOIN systypes t on c.xusertype=t.xusertype    
WHERE o.name = @Tables AND c.name = @Sort    

IF CHARINDEX('char', @type) > 0    
   SET @type = @type + '(' + CAST(@prec AS varchar) + ')'  
set @strSortColumn = @Sort +' '+@operator+' @SortColumn'
         
    
/*筛选以及分组语句.*/  
DECLARE @strPageSize varchar(50)    
DECLARE @strStartRow varchar(50)    
DECLARE @strFilter varchar(1000)    
DECLARE @strSimpleFilter varchar(1000)    
DECLARE @strGroup varchar(1000)    
   
IF @Filter IS NOT NULL AND @Filter != ''   
BEGIN   
SET @strFilter = ' WHERE ' + @Filter + ' '   
SET @strSimpleFilter = ' AND ' + @Filter + ' '   
END   
ELSE   
BEGIN   
SET @strSimpleFilter = ''   
SET @strFilter = ''   
END   
IF @Group IS NOT NULL AND @Group != ''   
SET @strGroup = ' GROUP BY ' + @Group + ' '   
ELSE   
SET @strGroup = ''   

SET @SqlCount = 'SELECT @TotalRecord=COUNT(*),@PageCount=CEILING((COUNT(*)+0.0)/'
+ CAST(@PageSize AS VARCHAR)+') FROM ' + @Tables + @strFilter + ' ' + @strGroup
EXEC SP_EXECUTESQL @SqlCount,N'@TotalRecord INT OUTPUT,@PageCount INT OUTPUT',
@TotalRecord OUTPUT,@PageCount OUTPUT

/*默认当前页*/    
IF @CurrentPage < 1  
SET @CurrentPage = 1    

if @CurrentPage > @PageCount and @PageCount<>0
SET @CurrentPage = @PageCount
   
/*设置分页参数.*/    
SET @strPageSize = CAST(@PageSize AS varchar(50))    
SET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS varchar(50))    

/*执行查询语句  */  

EXEC(
'DECLARE @SortColumn '+ @type + '   
SET ROWCOUNT ' + @strStartRow + '   
SELECT @SortColumn=' +@Sort + ' FROM ' + @Tables + @strFilter + '  ' + @strGroup + '  ORDER BY  ' + @SortStr + '   
SET ROWCOUNT ' + @strPageSize + '   
SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn +' '   
 + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @SortStr + ''   
)