分页存储过程

 IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[sp_Paging]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)DROP PROCEDURE [dbo].[sp_Paging]GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GO--分页存储过程开始/*此表中的PlanId是一个自动增长的字段*/CREATE PROCEDURE dbo.sp_Paging(@PageSize INT,@PageIndex INT --该过程接收两个参数，一个是当前页面一个是每页显示多少数量)ASBEGINDECLARE @COUNT INT --声明总数SELECT @COUNT=COUNT(SENDId) FROM [DB_Exam_11]..ExamPlan16SELECT @COUNT 'COUNT'DECLARE @STRSQL VARCHAR(2000)DECLARE @rFROM INT --声明从第几页开始SET @rFROM = (@PageIndex - 1) * @PageSizeIF @pageIndex > 1 --如果是从第二页或者大于以上开始，那么执行下面BEGIN SET @STRSQL = 'SELECT TOP ' + STR(@PageSize) + ' * FROM [DB_Exam_11]..ExamPlan16 WHERE [PlanId] > (SELECT MAX(PlanId) FROM (SELECT TOP ' + STR(@rFROM) + ' [PlanId] FROM [DB_Exam_11]..ExamPlan16 ORDER BY [PlanId] ) tempidb)'--上面sql得到的是当前页面所有记录ENDELSE --如果是从第一条记录 也就是第一页开始BEGIN SET @STRSQL = 'SELECT TOP ' + STR(@PageSize) + ' * FROM [DB_Exam_11]..ExamPlan16 ORDER BY [PlanId]'--获取TOP n条记录填充到当前页面 ，n=pagesizeENDexec(@STRSQL)--执行语句END--过程完毕GO--EXEC sp_Paging 1000,50SET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO