利用 row_number 进展对传入SQL语句进行分页

利用 row_number 进行对传入SQL语句进行分页

注意：

1. 传入的sql语句， 必须带order  by

2. 以前见过带Group by 的sql ， 直接用有问题， 修改后还是可以用， 但一时记不清情形了， 哪位看官有兴趣请指出， 大家共同切磋！

-- 创建测试表
CREATE TABLE TestTable
(
	[Id]    UNIQUEIDENTIFIER PRIMARY KEY,
	[Name]  VARCHAR(30)
)
GO

-- 加上9条测试数据
DELETE 
FROM   TestTable

DECLARE @rowIdx INT
SET @rowIdx = 1

WHILE (@rowIdx <= 9)
BEGIN
    INSERT INTO TestTable
      (
        Id,
        [NAME]
      )
    VALUES
      (
        NEWID(),
        '名字' + CONVERT(VARCHAR(2), @rowIdx)
      )
    SET @rowIdx = @rowIdx + 1
END

ALTER PROC PaginationForSQL
@sql VARCHAR(MAX),
@pageIndex INT,
@pageSize INT
    AS  
BEGIN
    DECLARE @newSql VARCHAR(MAX)  
    DECLARE @orderBy VARCHAR(100)  
    DECLARE @orderByIndex INT 
    
    --取得最后一个order by 的位置  
    SET @orderByIndex = 0  
    DECLARE @tempindex INT  
    WHILE (1 = 1)
    BEGIN
        SET @tempindex = CHARINDEX('order', @sql, @orderByIndex + 1)  
        IF (@tempindex = 0)
            BREAK
        
        SET @orderByIndex = @tempindex
    END  
    
    SET @orderBy = SUBSTRING(@sql, @orderByIndex, LEN(@sql) -@orderByIndex + 1)  
    SET @sql = SUBSTRING(@sql, 0, LEN(@sql) -LEN(@orderBy)) 
    
    --得到总记录数  
    SET @newSql = 'SELECT count(1) as Total FROM (' + @sql + ') AS t_outer ' 
    PRINT @newSql
    EXEC (@newSql) 
    --得到查询结果  
    SET @newSql = 'SELECT * FROM ( 
   SELECT ROW_NUMBER() OVER(' + @orderBy + ') AS RowNum,* FROM (' + @sql +
        ') AS t_inner  
) AS t_outer 
WHERE  RowNum > ' + CONVERT(VARCHAR(10), (@pageIndex -1) * @pageSize) +
        ' AND RowNum <= ' + CONVERT(VARCHAR(10), @pageIndex * @pageSize)
    
    PRINT @newSql  
    EXEC (@newSql)
END
GO

EXEC dbo.PaginationForSQL 'select * from TestTable order by name',2,4