sql server通过exec sp_executesql @pagecountsql,N'@RecodeNum int output',@RecodeNum
时间：2010-11-09 来源：lijinchang
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
相关阅读 更多 +
