获取指定页的记录 - 通用封装SQL脚本，制作简易报表

USE [dbo]
GO
/****** Object:  StoredProcedure [dbo].[Common_GetPageRecords]    Script Date: 2019-11-14 09:12:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
** 获取指定页的记录。
**/
ALTER PROCEDURE [dbo].[Common_GetPageRecords]
    @StartRow INT ,--起始行（从0开始）
    @MaxRows INT ,--每页的最大记录数
    @TableName NVARCHAR(2000) ,--表名
    @PrimaryKey NVARCHAR(50) ,--主键
    @GetFields NVARCHAR(1000) ,--要获取的列
    @SearchConditions NVARCHAR(2000) ,--搜索条件
    @SortExpression NVARCHAR(100) --排序表达式
AS /*默认排序*/
    IF @SortExpression IS NULL
        OR @SortExpression = ''
        SET @SortExpression = REPLACE(REPLACE(@PrimaryKey, '[', ''), ']', '');

    DECLARE @CurrentPage INT = 1;
    DECLARE @SortTable VARCHAR(100);
    DECLARE @SortName VARCHAR(100);
    DECLARE @strSortColumn VARCHAR(200);
    DECLARE @operator CHAR(2);
    DECLARE @type VARCHAR(100);
    DECLARE @prec INT;
    DECLARE @sql NVARCHAR(1000);
    DECLARE @paramDefine NVARCHAR(100);
    DECLARE @TotalRecordCount INT;
    DECLARE @RowCount INT;
    DECLARE @Group VARCHAR(1000) = NULL;

    IF @MaxRows = 0 
    BEGIN 
       RETURN
    END 
 /******************** 数据表中总记录数 ***********************/
    IF @PrimaryKey IS NOT NULL
        AND @PrimaryKey <> ''
        BEGIN
            SET @PrimaryKey = REPLACE(REPLACE(@PrimaryKey, '[', ''), ']', '');            
            SET @sql = 'SELECT @RowCount = COUNT(1) FROM ' + @TableName
            + ' WHERE '+@PrimaryKey+'<=0';
            SET @paramDefine = '@RowCount INT OUTPUT';
            EXEC sp_executesql @sql, @paramDefine, @RowCount OUTPUT;
                
            IF @RowCount>0
            BEGIN
                IF @SearchConditions IS NOT NULL
                AND @SearchConditions <> ''
                    BEGIN
                        SET @SearchConditions = @SearchConditions + ' AND '
                            + @PrimaryKey + '  !=-1 ';
                    END;
                ELSE
                    BEGIN
                        SET @SearchConditions = @PrimaryKey + ' !=-1 ';
                    END;
            END
        END;

    SET @sql = 'SELECT @TotalRecordCount = COUNT(*) FROM ' + @TableName;
        
    IF @SearchConditions IS NOT NULL
        AND @SearchConditions <> ''
        BEGIN        
            SET @sql = @sql + ' WHERE ' + @SearchConditions;
        END;        
  
    SET @paramDefine = '@TotalRecordCount INT OUTPUT';
    EXEC sp_executesql @sql, @paramDefine, @TotalRecordCount OUTPUT;
 /*************************************************************/

    IF ( @StartRow = 0 )
        SET @CurrentPage = 1;
    IF @MaxRows = -1
        BEGIN
            SET @CurrentPage = 1;
            SET @MaxRows = 10000000;
        END; 
    ELSE
        SET @CurrentPage = @StartRow / @MaxRows + 1;
 
 /*设定排序语句.*/
    IF CHARINDEX(' DESC', @SortExpression) > 0
        BEGIN             
            SET @strSortColumn = REPLACE(@SortExpression, ' DESC', '');
            SET @operator = '<=';
        END;  
    ELSE
        BEGIN 
            IF CHARINDEX(' ASC', @SortExpression) > 0
            BEGIN
                SET @strSortColumn = REPLACE(@SortExpression, ' ASC', '');
            END
            ELSE
            BEGIN
                SET @strSortColumn = @SortExpression;
            END 
            SET @operator = '>=';
        END;
     
    IF CHARINDEX('.', @strSortColumn) > 0
        BEGIN
            SET @SortTable = SUBSTRING(@strSortColumn, 0,
                                       CHARINDEX('.', @strSortColumn));
            SET @SortName = SUBSTRING(@strSortColumn,
                                      CHARINDEX('.', @strSortColumn) + 1,
                                      LEN(@strSortColumn));        
        END;
    ELSE
        BEGIN         
            SET @strSortColumn = ISNULL(@strSortColumn, @PrimaryKey);
            SET @SortTable = @TableName;    
            SET @SortName = @strSortColumn;
        END;
    
    -- table的内容还是要去掉dbo.和[]括号。
    IF CHARINDEX('.', @SortTable) > 0 OR CHARINDEX('[', @SortTable) > 0
        BEGIN
            SET @SortTable =REPLACE(REPLACE(SUBSTRING(@SortTable, CHARINDEX('.', @SortTable) + 1,
                                       LEN(@SortTable)),'[',''),']','')
        END

    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 = @SortTable
            AND c.name = @SortName;

    --SELECT @SortTable tab,@SortName sort, @type tty, @prec prc
    IF CHARINDEX('char', @type) > 0
    BEGIN
        IF @prec = -1  --如果排序的字段中有逗号存在时 @prec字段长度会为-1 就给它一个默认100长度
        BEGIN
            SET @type = @type + '(100)';
        END
        ELSE
        BEGIN
            SET @type = @type + '(' + CAST(@prec AS VARCHAR) + ')';
        END
    END

    DECLARE @strPageSize VARCHAR(50);
    DECLARE @strStartRow VARCHAR(50);
    DECLARE @strFilter VARCHAR(1000);
    DECLARE @strSimpleFilter VARCHAR(1000);
    DECLARE @strGroup VARCHAR(1000);
    
 /*设置分页参数.*/
    SET @strPageSize = CAST(@MaxRows AS VARCHAR(50));
    SET @strStartRow = CAST(( ( @CurrentPage - 1 ) * @MaxRows + 1 ) AS VARCHAR(50));
 /*筛选以及分组语句.*/
    IF @SearchConditions IS NOT NULL
        AND @SearchConditions != ''
        BEGIN
            SET @strFilter = ' WHERE ' + @SearchConditions + ' ';
            SET @strSimpleFilter = ' AND ' + @SearchConditions + ' ';
        END;
    ELSE
        BEGIN
            SET @strSimpleFilter = '';
            SET @strFilter = '';
        END;
    IF @Group IS NOT NULL
        AND @Group != ''
        SET @strGroup = ' GROUP BY ' + @Group + ' ';
    ELSE
        SET @strGroup = '';

    IF @type IS NULL
        SET @type = 'NVARCHAR(100)'; 
  --@SortColumn 为NULL 也要返回结果 20180911 Alma.Liu
 PRINT   '
    DECLARE @SortColumn ' + @type + '
    SET ROWCOUNT ' + @strStartRow + '
    SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @TableName + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @SortExpression + '
    SET ROWCOUNT ' + @strPageSize + '
    IF @SortColumn IS NOT NULL
    SELECT ' + @GetFields + ' FROM ' + @TableName + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @SortExpression + '
    ELSE
     SELECT ' + @GetFields + ' FROM ' + @TableName + ' WHERE 1=1 '  + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @SortExpression +'
    '
 /*执行查询语句*/
    EXEC(
    '
    DECLARE @SortColumn ' + @type + '
    SET ROWCOUNT ' + @strStartRow + '
    SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @TableName + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @SortExpression + '
    SET ROWCOUNT ' + @strPageSize + '
    IF @SortColumn IS NOT NULL
    SELECT ' + @GetFields + ' FROM ' + @TableName + ' WHERE ' + @strSortColumn + @operator +
     ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @SortExpression + '
    ELSE
     SELECT ' + @GetFields + ' FROM ' + @TableName + ' WHERE 1=1 ' + @strSimpleFilter 
     + ' ' + @strGroup + ' ORDER BY ' + @SortExpression + '
    '
    );

    
    RETURN @TotalRecordCount;