SQL Server分页显示数据的存储过程

create table student--建表
(
	sno varchar(10) primary key, 
	sname varchar(10), 
	sex char(2), 
	age int, 
	dept varchar(10)
)
--插入数据
insert into student values('1001','张三','男',20,'计算机')
insert into student values('1002','李四','男',18,'数学')
insert into student values('1003','赵六','女',19,'计算机')
insert into student values('1004','李斯','男',20, '数学')
insert into student values('1005','张三三','男',20,'计算机')
insert into student values('1006','李四四','男',18,'数学')
insert into student values('1007','赵六六','女',19,'计算机')
insert into student values('1008','李斯斯','男',20, '数学')
insert into student values('1009','李又斯','男',20, '数学')

go
--建立SQL Server中的分页显示存储过程，@tn：表名,@cnt：每页记录数；set rowcount 、exec的应用
create proc SplitPages(@tn nvarchar(30)='student', @cnt int=3) 
as
begin
	if object_id('temp') is not null drop table temp
	set @tn=quotename(@tn)--quotename函数使字符串成为有效的标识符
	if object_id(@tn) is null 
	begin
		print 'please check table name'
		return
	end
	exec ('select * into temp from ' + @tn)--执行动态SQL
	set rowcount @cnt
	select * from temp
	delete from temp
	while @@rowcount>0
	begin
		select * from temp
		delete from temp
	end
	set rowcount 0
	drop table temp
end
go

exec SplitPages--使用默认参数值调用
exec SplitPages 'student', 5--使用自定义参数调用