分页存储过程

USE [SYCN]
GO
/****** Object: StoredProcedure [dbo].[udt_page] Script Date: 2017/10/13 17:27:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

 

alter PROCEDURE [dbo].[udt_page]
@sql varchar(8000) = '', --进行查询的SQL语句
@page int,--页码
@pagesize int,--每页显示的条数
@pageCount int = 0 output,--总页数
@recordCount int = 0 output --记录总数
as
/*内容说明：翻页存储过程*/
set nocount on
declare @p1 int
exec sp_cursoropen @p1 output,@sql,@scrollopt=1,@ccopt=1,@rowcount=@pagecount output
set @recordCount = @pageCount
select @pagecount=ceiling(1.0*@pagecount/@pagesize)
,@page=(@page-1)*@pagesize+1
select @pageCount pageCount ,@recordCount recordCount
exec sp_cursorfetch @p1,16,@page,@pagesize
exec sp_cursorclose @p1