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