sqlserver分页

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

 

转载于:https://www.cnblogs.com/LoVeSW/p/3420434.html