sql server通过exec sp_executesql @pagecountsql,N'@RecodeNum int output',@RecodeNum output 传参执行自定义@sql...

USE [QYHYMeter]

GO

/****** Object:  StoredProcedure [dbo].[GetInforByPageIndex]    Script Date: 06/13/2010 16:04:46 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- exec [dbo].[GetInforByPageIndex]  'select [ID],[S_TiaoXingM],[S_ShuiBiaoID] from [QYHYMeter].[dbo].[BW_ShuiBiaoXX]' ,1,3

ALTER Proc   [dbo].[GetInforByPageIndex](

 @sql             nvarchar(max),--输入查询的sql

 @pageindex             int, --查询的页码

 @pagesize                int--查询的页增量

)

as

declare @indexstart int

declare @indexend int

declare @RecodeNum    int

declare @pagecount       int

declare @pagecountsql  nvarchar(max)

begin

set @pagecountsql='  select @RecodeNum=(select count(1) from ('+@sql+') t) '

print(@pagecountsql)

exec  sp_executesql @pagecountsql,N'@RecodeNum int output',@RecodeNum output

if @RecodeNum%@pagesize=0

begin

set @pagecount=@RecodeNum%@pagesize

end

else

begin

set @pagecount=@RecodeNum%@pagesize+1

end

if @pageindex<=@pagecount

begin

@indexstart=(@pageindex-1)*@pagesize+1

@indexend=@indexstart+@pagesize-1

end

else

begin

@indexstart=0

@indexend=@pagesize

end

  begin

  declare @indextable{  id int identity(1,1),

  s_id int}

  insert @indextable(s_id) select [ID]

     from [QYHYMeter].[dbo].[BW_ShuiBiaoXX]

 set rowcount @indexend --到@indexend行就停止查询

 select * from @indextable where id between @indexstart and @indexend

  end

end

转载于:https://www.cnblogs.com/lijinchang/archive/2010/11/09/1872444.html