1、分页存储过程的创建和调用。
-------------创建分页存储过程——————————
if exists(select * from sysobjects where name='pading') --判断存储过程pading是否存在
drop procedure pading --存在则删除
go
create procedure pading --创建存储过程pading
--设置参数
@currentPage int=0, --当前页码,默认值为0,即第一页
@pageSize int=5, --一页显示多少条,默认值为5
@countPage int output, --输出参数,总共多少页
@countRows int output --输出参数,总共多少记录
as
select top (@pageSize) * from temp --注意参数@pageSize需要用括号括起来
where id not in (select top (@pageSize*@currentPage) id from temp )
order by id
set @countRows=(select count(*) from temp) --赋值。计算总共有多少条记录
set @countPage=@countRows/@pageSize --赋值。计算总共有多少页
if @countRows%@pageSize<>0 --判断:如果总条数除以页显示条数不等于0
set @countPage=@countPage+1 --页码+1
---------------end------------------------------------------------------------------------
---------------执行分页存储过程-----------
declare @countpage int,@countrows int
exec pading 0,5,@countpage output,@countrows output
select @countpage,@countrows
---------------end------------------------
------------------------------创建分页存储过程(动态sql语句)------------------------------------
--发布时间:2011-08-26
--作者:qingyun1029
if exists (select * from sysobjects where name='splitpage')
drop procedure splitpage
go
create procedure splitpage
@pageSize int=10,
@currentPage int =0,
@countRecord int output,
@countPage int output
as
declare @sql nvarchar(1000)
--注意:拼接sql语句时,字符串类型不能与数字类型用+号拼接,需要用cast转换,如下:
set @sql=N'select top '+cast(@pageSize as nvarchar(10))
+' * from temp where id not in (select top '
+cast((@pageSize*@currentPage) as nvarchar(10))+' id from temp)'
exec sp_executesql @sql
set @countRecord=(select count(*) from temp)
set @countPage=@countRecord/@pageSize
if(@countRecord%@pageSize<>0)
set @countPage=@countPage+1
------------------------------调用存储过程------------------------------------
declare @cRecord int, @cPage int
exec splitpage 500,2,@cRecord output,@cPage output
select @cRecord,@cPage
--------------------------------end-----------------------------------------------
2、sql中使用循环,插入测试数据
declare @star int
set @star=0
while @star<100000
begin
insert into temp
values('qingyun',24)
set @star=@star+1
end