CREATE PROCEDURE p_GetStudentInfo
@PageSize INT,
@PageIndex INT,
@strWhere varchar(1500) -- 查询条件(注意: 不要加where)
As
Begin
select * from (
select row_number() over (order by StudentInfoId) row,* from StudentInfo ) StudentInfo
where row between @PageSize*(@PageIndex-1) and @PageSize*@PageIndex
End
go
通用方法
USE [OrderDB]
GO
/****** Object: StoredProcedure [dbo].[sp_splitpage] Script Date: 03/20/2012 13:46:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_splitpage]
@sql NVARCHAR(4000),--要执行的sql语句
@page INT = 1, --要显示的页码
@pageSize INT, --每页的大小
@pageCount INT = 0 OUT, --总页数
@recordCount INT = 0 OUT --总记录数
AS
Begin
DECLARE @usetime DATETIME
IF @page = 0
BEGIN
SET @page = 1
END
SET @usetime = GETDATE()
SET nocount ON
DECLARE @p1 INT
EXEC sp_cursoropen @p1 OUTPUT, @sql, @scrollopt = 1, @ccopt = 1,
@rowcount = @pagecount OUTPUT
SET @recordCount = @pageCount
IF ( @page > CEILING(1.0 * @pagecount / @pagesize) )
BEGIN
SET @page = CEILING(1.0 * @pagecount / @pagesize)
END
SELECT @pagecount = CEILING(1.0 * @pagecount / @pagesize),
@page = ( @page - 1 ) * @pagesize + 1
EXEC sp_cursorfetch @p1, 16, @page, @pagesize
EXEC sp_cursorclose @p1
End