Mssql分页存储过程(任意字段排序)

USE [Demo]
GO
/****** 对象:  StoredProcedure [dbo].[GetDataByPage]    脚本日期: 09/27/2008 17:32:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/******************************************************************
 * Author: jommy
 * CreatedDate: 09/14/2008
 * StoredProcedureName: GetDataByPage
 * Description: Access to the designated page of data.
 *****************************************************************/
CREATE PROCEDURE [dbo].[GetDataByPage]
(
    @tabName varchar(4000),         -- Table name,ex:'(SELECT * FROM T1 INNER JOIN T2 ON T1.XX=T2.XX) M' OR 'T1'
    @primaryKey varchar(150),       -- Table primary key
    @sort varchar(1000) = '',       -- Sort field
    @pageIndex int = 1,             -- The current page
    @pageSize int = 10,             -- Page size
    @fields varchar(1000) = '*',    -- View fields
    @filter varchar(4000) = '',     -- The conditions
    @rowCount int = 0,              -- The records
    @doCount bit = 0                -- Do count
)
AS
    SET NOCOUNT ON;
    DECLARE @mainSQL varchar(4000)
    DECLARE @orderColumns varchar(1000),@orderColumn varchar(100)
    DECLARE @charindex int, @hasPrimaryKey bit,@isNumericSort bit
    DECLARE @strPageSize varchar(20), @strStartRow varchar(20), @factRows int
    DECLARE @reverseColumns varchar(1000)
    
    SET @isNumericSort = 0
    SET @primaryKey = REPLACE(@primaryKey,' ','')
    SET @orderColumn = ''

    IF CHARINDEX('(',@tabName) = 0
        BEGIN
            SET @orderColumns = REPLACE(REPLACE(REPLACE(@sort,' DESC',''),' ASC',''),' ','')
            IF (LEN(@sort)-LEN(REPLACE(@sort,',',''))) = 0 AND (@orderColumns = @primaryKey OR @orderColumns = '')
            BEGIN
                DECLARE @type nvarchar(128),@dbName varchar(50),@typeSQL nvarchar(1000)
                SET @dbName = ''
                IF (LEN(@tabName)-LEN(REPLACE(@tabName,'.',''))) >= 2
                    SET @dbName = LEFT(@tabName,CHARINDEX('.',@tabName))
                    
                SET @dbName = LEFT(@tabName,CHARINDEX('.',@tabName))
            
                SET @typeSQL = 'SELECT @type = t.name FROM ' + @dbName + 'sys.sysobjects o 
JOIN ' + @dbName + 'sys.syscolumns c on o.id=c.id
JOIN ' + @dbName + 'sys.systypes t on c.xusertype=t.xusertype
WHERE o.name = ''' + @tabName + ''' AND c.name = ''' + @primaryKey + ''''
                EXECUTE sp_executesql @typeSQL, N'@type nvarchar(128) OUTPUT',@type = @type OUTPUT
                IF @type = 'bigint' OR @type = 'decimal ' OR @type = 'int' OR @type = 'numeric'
                    SET @isNumericSort = 1
            
                IF @isNumericSort = 1
                    BEGIN
                        IF CHARINDEX(' ASC',@sort) >= 0 SET @orderColumn = 'MAX'
                        IF CHARINDEX(' DESC',@sort) > 0 SET @orderColumn = 'MIN'
                    END
            END
        END
        

    /* Validate variables */    
    IF @pageIndex < 1 SET @pageIndex = 1
    IF @filter <> '' SET @filter = ' WHERE ' + @filter

    SET @mainSQL = ' FROM ' + @tabName + @filter
    
    /* Count rows */
    IF @doCount = 1 
        BEGIN
            EXECUTE('SELECT COUNT(*) AS Total' + @mainSQL)
            RETURN;
        END

    IF @isNumericSort = 1
        BEGIN
            DECLARE @sortFilter varchar(200)
            SET @sortFilter = ''
            SET @orderColumns = ' ORDER BY '
            IF @sort = '' SET @orderColumns = @orderColumns + @primaryKey ELSE SET @orderColumns = @orderColumns + @sort
            
            IF @filter = '' 
                SET @filter = ' WHERE 1=1'

            IF @pageIndex > 1
                BEGIN
                SET @factRows = @pageSize * (@pageIndex - 1)
                SET @strPageSize = CAST(@factRows AS varchar(20))
                DECLARE @limitSQL nvarchar(4000)
                SET @limitSQL = 'SELECT @strStartRow = ' +  @orderColumn + '(' + @primaryKey + ')' + ' FROM ( ' +
                                'SELECT TOP ' + @strPageSize + ' ' + @primaryKey + @mainSQL + @orderColumns +
                                ') M'
                EXECUTE sp_executesql @limitSQL, N'@strStartRow varchar(20) OUTPUT',@strStartRow = @strStartRow OUTPUT
                END

            IF @orderColumn = 'MAX' SET @orderColumn = '>'
            IF @orderColumn = 'MIN' SET @orderColumn = '<'

            IF @pageIndex > 1
                SET @sortFilter = ' AND ' + @primaryKey + @orderColumn + @strStartRow

            SET @mainSQL = ' FROM ' + @tabName + @filter
            SET @strPageSize = CAST(@pageSize AS varchar(20))

            /* Execute dynamic query */ 
            EXECUTE ('SELECT TOP ' + @strPageSize + ' ' + @fields + @mainSQL +
                         @sortFilter + @orderColumns)

        END
    ELSE
        BEGIN
        IF @rowCount = 0
            BEGIN
                DECLARE @countSQL nvarchar(4000)
                SET @countSQL = 'SELECT @rowCount = COUNT(*)' + @mainSQL
                EXECUTE sp_executesql @countSQL, N'@rowCount int OUTPUT',
                        @rowCount = @rowCount OUTPUT
            END

        
        /* Check fact rows */
        SET @factRows = @rowCount - (@pageIndex - 1) * @pageSize
        IF @factRows > @pageSize SET @factRows = @pageSize

        /* Validate factRow */
        IF @factRows < 0 RETURN;

        /* Set paging variables */
        SET @strPageSize = CAST(@factRows AS varchar(20))
        SET @strStartRow = CAST((@pageIndex * @pageSize) AS varchar(20))

        /* Has primary key */
        SET @hasPrimaryKey = 0
        IF @sort <> '' SET @sort = @sort + ',0' -- Simulate sort keys
        SET @orderColumns = ''
        SET @charindex = CHARINDEX(',',@sort)
        WHILE @charindex > 0
            BEGIN
                SET @orderColumn = LEFT(@sort, @charindex - 1)
                IF @primaryKey = REPLACE(REPLACE(REPLACE(@orderColumn,' DESC',''),' ASC',''),' ','')
                    SET @hasPrimaryKey = 1
                IF CHARINDEX(' ASC',@orderColumn) = 0 AND CHARINDEX(' DESC',@orderColumn) = 0
                    SET @orderColumn = @orderColumn + ' ASC'
                SET @orderColumns = @orderColumns + @orderColumn + ','
                SET @sort = RIGHT(@sort, LEN(@sort) - @charindex)
                SET @charindex = CHARINDEX(',',@sort)
            END

        IF @sort IS NULL OR  @sort = ''
            BEGIN
                SET @orderColumns = @primaryKey + ' ASC'
                SET @hasPrimaryKey = 1
            END
        ELSE
            SET @orderColumns = LEFT(@orderColumns,LEN(@orderColumns) - 1)

        /* Inject primary key */
        IF @hasPrimaryKey = 0
            BEGIN
                SET @orderColumns = @orderColumns +',' + @primaryKey + ' ASC'
            END
        
        /* Reverse sort key  */
        SET @reverseColumns = REPLACE(@orderColumns,' ASC',' !@#')
        SET @reverseColumns = REPLACE(@reverseColumns,' DESC',' ASC')
        SET @reverseColumns = REPLACE(@reverseColumns,' !@#',' DESC')


        /* Execute dynamic query */ 
        EXECUTE ('SELECT * FROM (
                    SELECT TOP ' + @strPageSize + ' * FROM (
                        SELECT TOP ' + @strStartRow + @fields + @mainSQL + ' ORDER BY ' + @orderColumns + '
                    ) M ORDER BY ' + @reverseColumns + '
                  ) M ORDER BY ' + @orderColumns)

        END