in coding

mssql分页存储过程.2

基于游标的分页对很多情况不能适用,这是一个利用临时表构建的分页存储过程

CREATE PROC [dbo].[pre_P_ListRecordOrderByID]
    @page INT,
    @pageSize INT
AS
    SET NOCOUNT ON 

    DECLARE @beginId INT, @endId INT, @pageCount INT

    CREATE TABLE #tmp_PagedRecordOrderByID
    (
        XID INT IDENTITY ,
        ID INT NOT NULL ,
        WinTimes INT NOT NULL DEFAULT 0,
        DefeatTimes INT NOT NULL DEFAULT 0,
        Area VARCHAR(100) NULL ,
        Server VARCHAR(100) NULL
    )

    INSERT INTO #tmp_PagedRecordOrderByID (ID,[Group],WinTimes,DefeatTimes,Area,Server)
    SELECT ID,[Group],WinTimes,DefeatTimes,Area,Server
    FROM pre_Statistic
    ORDER BY ID DESC

    SELECT @pageCount = COUNT(ID) FROM gch_Statistic
    SELECT @pageCount = CEILING(@pageCount/@pageSize) + 1
    SELECT @beginId = (@page-1) * @pageSize + 1
    SELECT @endId = @beginId + @pageSize -1

    SELECT ID,[Group],WinTimes,DefeatTimes,Area,Server
    FROM #tmp_PagedRecordOrderByID
    WHERE XID BETWEEN @beginId AND @endId

    DROP TABLE #tmp_PagedRecordOrderByID
    SET NOCOUNT OFF

    RETURN @pageCount

GO

不知道有没有比较通用的分页存储过程呢?

Write a Comment

Comment

  • Related Content by Tag