Sqlserver和Oracle通用标准分页存储过程

-- =============================================
-- Author:        唐德建
-- Create date: 2018/02/13 09:14:28
-- Description: 根据特定条件进行查询，返回结果支持分页
-- =============================================
ALTER PROC [dbo].[UP_Admin_Query]
@Count INT = 0,
@Index INT = 0,
@Total INT OUTPUT,
@Name nvarchar(20) = null

AS
BEGIN
    DECLARE @startNum INT, @endNum INT, @pageCount INT
    
    --创建临时表根据特定条件筛选数据
    DECLARE @TempTable Table    
    (
    RowNum INT,    
    Id INT
    )    
    INSERT INTO @TempTable 
        SELECT ROW_NUMBER() OVER(ORDER BY EditTime DESC) AS RowNum,Id 
        FROM dbo.[Admin]
        WHERE [DeletedTime] IS NULL 
          AND  (@Name is null or [Name] like '%'+@Name+'%')

    --总的数据条数    
    SELECT @Total = COUNT(1) FROM @TempTable 
    

    --可分的页数总和
    SET @pageCount = @Total/@Count
    


    SET @startNum = (@Index - 1) * @Count + 1    

    SET @endNum = @startNum + @Count -1 
    
    SELECT 
        A.[Id],A.[RoleId],A.[Name],A.[Gender],A.[LoginName],A.[Password],A.[Cellphone],A.[Email],A.[Status],A.[InTime],A.[EditTime],A.[InAdmin],A.[EditAdmin],A.[DeletedTime]
    FROM dbo.[Admin] AS A
    INNER JOIN (SELECT RowNum,Id 
                FROM @TempTable
                WHERE RowNum BETWEEN @startNum AND @endNum) AS T
    ON A.Id = T.Id
END