110,538
社区成员
发帖
与我相关
我的任务
分享
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
/*--调用示例
declare @PageCount int
exec sp_PageView
@sql='select * from sysobjects',
@PageCurrent=2,
@PageCount=@PageCount out
SELECT @PageCount
--*/
ALTER PROC [dbo].[sp_PageView]
@sql ntext, --要执行的sql语句
@PageCurrent int=1, --要显示的页码
@PageSize int=10, --每页的大小
@PageCount int OUTPUT --总页数
AS
SET NOCOUNT ON
DECLARE @p1 int
--初始化分页游标
EXEC sp_cursoropen
@cursor=@p1 OUTPUT,
@stmt=@sql,
@scrollopt=1,
@ccopt=1,
@rowcount=@PageCount OUTPUT
--计算总页数
IF ISNULL(@PageSize,0)<1
SET @PageSize=10
SET @PageCount=(@PageCount+@PageSize-1)/@PageSize
IF ISNULL(@PageCurrent,0)<1 OR ISNULL(@PageCurrent,0)>@PageCount
SET @PageCurrent=1
ELSE
SET @PageCurrent=(@PageCurrent-1)*@PageSize+1
select @PageCount
--显示指定页的数据
EXEC sp_cursorfetch @p1,16,@PageCurrent,@PageSize
--关闭分页游标
EXEC sp_cursorclose @p1
DataSet ds = new DataSet();
IDataParameter[] iData = new SqlParameter[4];
iData[0] = new SqlParameter("@sql", strSQL);
iData[1] = new SqlParameter("@PageCurrent", strPageCurrent);
iData[2] = new SqlParameter("@PageSize", strPageSize);
iData[3] = new SqlParameter("@PageCount", 1);
iData[3].Direction = ParameterDirection.Output;
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(conn, "sp_PageView", iData);
sqlDA.Fill(ds, "NewTable");
intRowCount = (Int32)iData[3].Value;