通用的存储过程分页实现思路--带源码（适用Ms SQL Server 2005）

 

分页是我们开发过程中最常用到的功能，实现方式多种多样，根据项目开发中的体验，还是用存储过程分页比较不错，不管在性能上，还是方便性上都是个不错的选择。下面是用存储过程实现分页的一个思路，把源代码贴出来和大家分享。

转载或修改请注明出处，很辛苦弄出来的，哈哈^_^ 

USE [tempdb]
GO
/**//****** Object:  StoredProcedure [dbo].[PROC_COMM_PAGE_NAVIGATION]    Script Date: 02/27/2007 16:32:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



/**//*
********************************************
function        :    page navigation 

create by        :    

create at        :    2007-02-10 

modify    by        :   

modify at        :   

remark            :   
  
case            :    

copyright        :    http://blog.youkuaiyun.com/lxjhb

********************************************
*/ 

CREATE PROCEDURE [dbo].[PROC_COMM_PAGE_NAVIGATION]
    @TABLE_NAME VARCHAR(8000),        --table name or view     
    @PK_NAME VARCHAR(50),            --prime key column name      
    @SORT_COLUMN_NAME VARCHAR(50),    --sort column      
    @SORT_TYPE VARCHAR(10),            --sort type:asc,desc      
    @CURRENT_PAGE INT,                --current page       
    @PAGE_SIZE INT                    --page size      
AS      
    
SET NOCOUNT ON    
BEGIN    
    DECLARE @LONGSQL  VARCHAR(8000)    
          , @SHORTSQL NVARCHAR(4000)    
          , @TAB VARCHAR(40)    
          , @RECORD_COUNT INT    
        
    SELECT @TAB = 'PRT_' + REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),13),':','') ,' ','')     
                  + '_' + CONVERT(VARCHAR,@@SPID)    
        
    SELECT @LONGSQL =    
        'SELECT IDENTITY(INT,1,1) T1RNUM, T.* ' +    
           ' INTO TEMPDB.DBO.'+ @TAB +    
        '  FROM ( ' + @TABLE_NAME + ' ) T' +    
           '  ORDER BY T.'+@SORT_COLUMN_NAME+' ' +     
           CASE WHEN @SORT_TYPE <> 'DESC' THEN 'ASC'    
                ELSE @SORT_TYPE END    
        
    EXEC(@LONGSQL)      
        
    SELECT @RECORD_COUNT =@@ROWCOUNT     
        
    SELECT @SHORTSQL = 'SELECT ' +    
           MAX(CASE WHEN COLUMN_ID = 2  THEN '  ' + C.NAME ELSE '' END) +    
           MAX(CASE WHEN COLUMN_ID = 3  THEN ', ' + C.NAME ELSE '' END) +    
           MAX(CASE WHEN COLUMN_ID = 4  THEN ', ' + C.NAME ELSE '' END) +    
           MAX(CASE WHEN COLUMN_ID = 5  THEN ', ' + C.NAME ELSE '' END) +    
           MAX(CASE WHEN COLUMN_ID = 6  THEN ', ' + C.NAME ELSE '' END) +    
           MAX(CASE WHEN COLUMN_ID = 7  THEN ', ' + C.NAME ELSE '' END) +    
           MAX(CASE WHEN COLUMN_ID = 8  THEN ', ' + C.NAME ELSE '' END) +    
           MAX(CASE WHEN COLUMN_ID = 9  THEN ', ' + C.NAME ELSE '' END) +    
           MAX(CASE WHEN COLUMN_ID = 10 THEN ', ' + C.NAME ELSE '' END) +    
           MAX(CASE WHEN COLUMN_ID = 11 THEN ', ' + C.NAME ELSE '' END) +    
           MAX(CASE WHEN COLUMN_ID = 12 THEN ', ' + C.NAME ELSE '' END) +    
           MAX(CASE WHEN COLUMN_ID = 13 THEN ', ' + C.NAME ELSE '' END) +    
           MAX(CASE WHEN COLUMN_ID = 14 THEN ', ' + C.NAME ELSE '' END) +    
           MAX(CASE WHEN COLUMN_ID = 15 THEN ', ' + C.NAME ELSE '' END) +    
           MAX(CASE WHEN COLUMN_ID = 16 THEN ', ' + C.NAME ELSE '' END) +    
           MAX(CASE WHEN COLUMN_ID = 17 THEN ', ' + C.NAME ELSE '' END) +    
           MAX(CASE WHEN COLUMN_ID = 18 THEN ', ' + C.NAME ELSE '' END) +    
           MAX(CASE WHEN COLUMN_ID = 19 THEN ', ' + C.NAME ELSE '' END) +    
           MAX(CASE WHEN COLUMN_ID = 20 THEN ', ' + C.NAME ELSE '' END) +    
           MAX(CASE WHEN COLUMN_ID = 21 THEN ', ' + C.NAME ELSE '' END) +    
           MAX(CASE WHEN COLUMN_ID = 22 THEN ', ' + C.NAME ELSE '' END) +    
           MAX(CASE WHEN COLUMN_ID = 23 THEN ', ' + C.NAME ELSE '' END) +    
           MAX(CASE WHEN COLUMN_ID = 24 THEN ', ' + C.NAME ELSE '' END) +    
           MAX(CASE WHEN COLUMN_ID = 25 THEN ', ' + C.NAME ELSE '' END) +    
           MAX(CASE WHEN COLUMN_ID = 26 THEN ', ' + C.NAME ELSE '' END) +  
           MAX(CASE WHEN COLUMN_ID = 27 THEN ', ' + C.NAME ELSE '' END) +  
           MAX(CASE WHEN COLUMN_ID = 28 THEN ', ' + C.NAME ELSE '' END) +  
           MAX(CASE WHEN COLUMN_ID = 29 THEN ', ' + C.NAME ELSE '' END) +  
           MAX(CASE WHEN COLUMN_ID = 30 THEN ', ' + C.NAME ELSE '' END) +  
           '  FROM TEMPDB.DBO.'+ @TAB +    
        ' WHERE T1RNUM >  ' +     
           CONVERT(VARCHAR,    
           (CASE WHEN @CURRENT_PAGE <= 0 THEN 1    
                 WHEN @CURRENT_PAGE >=
                      CEILING(CONVERT(DECIMAL(15,2),@RECORD_COUNT)/@PAGE_SIZE)
                 THEN CEILING(CONVERT(DECIMAL(15,2),@RECORD_COUNT)/@PAGE_SIZE)
                 ELSE @CURRENT_PAGE    
            END-1) * @PAGE_SIZE) +    
        '   AND T1RNUM <= ' +     
           CONVERT(VARCHAR,    
           CASE WHEN @CURRENT_PAGE <= 0 THEN 1    
                WHEN @CURRENT_PAGE >= 
                     CEILING(CONVERT(DECIMAL(15,2),@RECORD_COUNT)/@PAGE_SIZE)
                THEN CEILING(CONVERT(DECIMAL(15,2),@RECORD_COUNT)/@PAGE_SIZE)
                ELSE @CURRENT_PAGE    
            END * @PAGE_SIZE)    
      FROM TEMPDB.SYS.OBJECTS O    
      , TEMPDB.SYS.COLUMNS C    
     WHERE O.TYPE = 'U'    
       AND O.OBJECT_ID = C.OBJECT_ID    
       AND O.NAME = @TAB    

    --return result      
    SELECT @RECORD_COUNT AS 'RECORD_COUNT',      
           CEILING(CONVERT(DECIMAL(15,2),@RECORD_COUNT)/@PAGE_SIZE) AS 'PAGE_COUNT',      
           CASE WHEN @CURRENT_PAGE <= 0 THEN 1    
                WHEN @CURRENT_PAGE >= 
                     CEILING(CONVERT(DECIMAL(15,2),@RECORD_COUNT)/@PAGE_SIZE)
                THEN CEILING(CONVERT(DECIMAL(15,2),@RECORD_COUNT)/@PAGE_SIZE)
                ELSE @CURRENT_PAGE    
            END AS 'CURRENT_PAGE',      
           @PAGE_SIZE    AS 'PAGE_SIZE',      
           @CURRENT_PAGE AS 'PAGE_IN'      
        
    EXEC DBO.SP_EXECUTESQL @SHORTSQL    

    --drop temp table  
    SELECT @SHORTSQL = 'DROP TABLE TEMPDB.DBO.' + @TAB    
    EXEC DBO.SP_EXECUTESQL @SHORTSQL 

END

SET NOCOUNT OFF