【SQLSERVER学习笔记】分页存储过程+调用

USE [数据库名]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[存储过程名]
    @pageIndex INT,
    @pageSize INT,    
    @totalCount INT OUTPUT    
AS 
SET NOCOUNT ON

DECLARE @PageStart INT
DECLARE @PageEnd   INT

SET @pageStart=(@pageIndex-1)*@pageSize
SET @pageEnd=@pageIndex*@pageSize
BEGIN 
    SELECT @totalCount=COUNT(1) FROM [dbo].[表名]
    SELECT 
         [Id]
        ,字段1
        ,字段2
        ,.....        
    FROM 
        (SELECT 
            ROW_NUMBER() OVER (ORDER BY 字段名) AS RowNumber,
             [Id]
            ,[Name]
            ,[Phone]
            ,[Age]
            ,[CreateTime] 
        FROM [dbo].[表名] WITH(NOLOCK)) AS Temp
    WHERE Temp.RowNumber>@PageStart AND Temp.RowNumber<@PageEnd
    ORDER BY Temp.CreateTime DESC,Temp.Name
END

GO

调用此存储过程的方法：

USE [数据库名]
GO
DECLARE @totalResult INT
EXEC [存储过程名] 1,10,@totalResult output
SELECT @totalResult