分页是我们开发过程中最常用到的功能，实现方式多种多样，根据项目开发中的体验，还是用存储过程分页比较不错，不管在性能上，还是方便性上都是个不错的选择。下面是用存储过程实现分页的一个思路，把源代码贴出来和大家分享。
转载或修改请注明出处，很辛苦弄出来的，哈哈^_^
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.csdn.net/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
