Identity分页
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE name='PRO_IDENTITYPAGEDATE') DROP PROCEDURE PRO_IDENTITYPAGEDATE GO CREATE PROCEDURE PRO_IDENTITYPAGEDATE @TABLENAME VARCHAR(36),--表名 @ORDERBYNAME VARCHAR(36),--排序列名 @PRIMARYKEY VARCHAR(36),--主键 @PAGESIZE INT,--显示的条数 @PAGENOW INT,--当前页 @PAGECOUNT INT OUT,--总页数 @ROWCOUNT INT OUT--总行数 AS SELECT @ROWCOUNT=COUNT(*) FROM Users-- ORDER BY Id; IF @ROWCOUNT%@PAGESIZE=0 BEGIN SET @PAGECOUNT=@ROWCOUNT/@PAGESIZE END ELSE BEGIN SET @PAGECOUNT=@ROWCOUNT/@PAGESIZE+1; END IF @PAGENOW>=@PAGECOUNT BEGIN SET @PAGENOW=@PAGECOUNT END IF @PAGENOW<=1 BEGIN SET @PAGENOW=1 END SELECT IDENTITY(INT,1,1) AS SCORT,CONVERT(VARCHAR(10),@PRIMARYKEY) ID INTO #TABLE FROM @TABLENAME SELECT * FROM #TABLE WHERE SCORT>(@PAGENOW-1)*@PAGESIZE AND SCORT<=(@PAGENOW)*@PAGESIZE ORDER BY @ORDERBYNAME DROP TABLE #TABLE
ROWNUMBER分页针对sqlserver2005以上
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE name='PRO_ROWNUMBERPAGEDATE') DROP PROCEDURE PRO_ROWNUMBERPAGEDATE GO CREATE PROCEDURE PRO_ROWNUMBERPAGEDATE @TABLENAME VARCHAR(36),--表名 @ORDERBYNAME VARCHAR(36),--排序列名 @PAGESIZE INT,--显示的条数 @PAGENOW INT,--当前页 @PAGECOUNT INT OUT,--总页数 @ROWCOUNT INT OUT--总行数 AS SELECT @ROWCOUNT=COUNT(*) FROM Users-- ORDER BY Id; IF @ROWCOUNT%@PAGESIZE=0 BEGIN SET @PAGECOUNT=@ROWCOUNT/@PAGESIZE END ELSE BEGIN SET @PAGECOUNT=@ROWCOUNT/@PAGESIZE+1; END IF @PAGENOW>=@PAGECOUNT BEGIN SET @PAGENOW=@PAGECOUNT END IF @PAGENOW<=1 BEGIN SET @PAGENOW=1 END SELECT * FROM ( SELECT *,ROW_NUMBER() OVER(ORDER BY @ORDERBYNAME) AS ROW FROM @TABLENAME ) T WHERE T.ROW>(@PAGENOW-1)*@PAGESIZE AND T.ROW<=(@PAGENOW)*@PAGESIZE
Top分页
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE name='PRO_TOPPAGEDATE') DROP PROCEDURE PRO_TOPPAGEDATE GO CREATE PROCEDURE PRO_TOPPAGEDATE @TABLENAME VARCHAR(36),--表名 @ORDERBYNAME VARCHAR(36),--排序列名 @PRIMARYKEY VARCHAR(36),--主键 @PAGESIZE INT,--显示的条数 @PAGENOW INT,--当前页 @PAGECOUNT INT OUT,--总页数 @ROWCOUNT INT OUT--总行数 AS SELECT @ROWCOUNT=COUNT(*) FROM @TABLENAME-- ORDER BY Id; IF @ROWCOUNT%@PAGESIZE=0 BEGIN SET @PAGECOUNT=@ROWCOUNT/@PAGESIZE END ELSE BEGIN SET @PAGECOUNT=@ROWCOUNT/@PAGESIZE+1; END IF @PAGENOW>=@PAGECOUNT BEGIN SET @PAGENOW=@PAGECOUNT END IF @PAGENOW<=1 BEGIN SET @PAGENOW=1 END SELECT TOP(@PAGESIZE) * FROM @TABLENAME WHERE @PRIMARYKEY NOT IN ( SELECT TOP((@PAGENOW-1)*@PAGESIZE) @PRIMARYKEY FROM @TABLENAME ORDER BY @ORDERBYNAME ) ORDER BY @ORDERBYNAME