SqlServer分页存储过程示例

USE [Micro_Soft] 

go 
--存在就删除旧存储过程
IF EXISTS (SELECT * 
           FROM   sys.objects 
           WHERE  object_id = Object_id(N'[dbo].[prPager]') 
                  AND type IN ( N'P', N'PC' )) 
  DROP PROCEDURE [dbo].[prPager] 
go 

USE [Micro_Soft] 

go 

SET ansi_nulls ON 

go 

SET quoted_identifier ON 

go 

CREATE PROCEDURE [dbo].[Prpager] @varIdentityName VARCHAR(200),--主键名称 
                                 @intPageSize     INT,----每页记录数 
                                 @intCurrentCount INT, 
                                 ----当前记录数量（页码*每页记录数） 
                                 @varTableName    VARCHAR(200),----表名称 
                                 @varWhere        VARCHAR(800),----查询条件 
                                 @intTotalCount   INT output ----记录总数 
AS 
    DECLARE @sqlSelect NVARCHAR(2000) 
    ----局部变量（sql语句），查询记录集 
    DECLARE @sqlGetCount NVARCHAR(2000) 
    ----局部变量（sql语句），取出记录集总数 
    
    SET @sqlSelect = 'SELECT * FROM  ' 
                     + '    (SELECT ROW_NUMBER()  OVER( ORDER BY ' 
                     + @varIdentityName + ') AS RowNumber,* ' 
                     + '        FROM ' + @varTableName 
                     + '        WHERE ' + @varWhere 
                     + '     ) as  T2 ' + ' WHERE T2.RowNumber<= ' 
                     + Str(@intCurrentCount + @intPageSize) 
                     + ' AND T2.RowNumber>' 
                     + Str(@intCurrentCount) 
    SET @sqlGetCount = 'SELECT @intTotalCount = COUNT(*) FROM ' 
                       + @varTableName + ' WHERE ' + @varWhere 

    EXEC (@sqlSelect) 

    EXEC Sp_executesql @sqlGetCount, N'@intTotalCount INT OUTPUT', @intTotalCount output
go