按行读取
CREATE PROCEDURE GetDataByLine
(
--创建一个分页读取过程
@SqlStr varchar(8000), --SQL语句
@FirstRec int, --页起始行
@LastRec int --页结束行
)
AS
DECLARE @dt varchar(10) --生成临时表的随机数
BEGIN
SELECT @dt= substring(convert(varchar, rand()), 3, 10) --一个字符型的随机数
--将搜索结果放入临时表中,表名随机生成,在' FROM '前插入'INTO '+随机临时表名
SELECT @SqlStr = stuff(@SqlStr, charindex(' FROM ', upper(@SqlStr)), 6 ,' INTO tempdb..Lining' + @dt + ' FROM ')
EXECUTE (@SqlStr)
--为临时表增加id号
SELECT @SqlStr = 'ALTER TABLE tempdb..Lining' + @dt + ' ADD TEMPDB_ID numeric(10) IDENTITY PRIMARY KEY'
EXECUTE (@SqlStr)
--计算临时表中的记录数
--SELECT @SqlStr = 'SELECT Count(*) From tempdb..Lining' + @dt
--EXECUTE (@SqlStr)
--选取记录号在起始行和结束行中间的记录
SELECT @SqlStr = 'SELECT * FROM tempdb..Lining'+@dt+' WHERE TEMPDB_ID > ' + convert(varchar, @FirstRec) + ' and TEMPDB_ID <= ' + convert(varchar, @LastRec)
EXECUTE (@SqlStr)
--删除临时表
SELECT @SqlStr = 'DROP TABLE tempdb..Lining'+@dt
EXECUTE (@SqlStr)
END
/*
some comments:
1.@SqlStr varchar(8000), depends on your page size
2. this is a generic paging sp, if you just want to use it for specific table,
you'd better change the 'tempdb..Lining' to #Paging, the performance will be better
*/
按页读取
CREATE PROCEDURE GetDataByPage
(
--创建一个分页读取过程
@SqlStr varchar(8000), --SQL语句
@PageSize int, --每页记录数
@CurrentPage int --当前页数
)
AS
DECLARE @FirstRec int, @LastRec int, @dt varchar(10) --页起始行,页结束行,生成临时表的随机数
BEGIN
SELECT @FirstRec = (@CurrentPage - 1) * @PageSize --计算页起始行
SELECT @LastRec = (@CurrentPage * @PageSize + 1) --计算页结束行
SELECT @dt= substring(convert(varchar,rand()),3,10) --一个字符型的随机数
--将搜索结果放入临时表中,表名随机生成,在' FROM '前插入'INTO '+随机临时表名
SELECT @SqlStr = stuff(@SqlStr, charindex(' FROM ',upper(@SqlStr)), 6 ,' INTO tempdb..Paging'+@dt+' FROM ')
EXECUTE (@SqlStr)
--为临时表增加id号
SELECT @SqlStr = 'ALTER TABLE tempdb..Paging'+@dt+' ADD TEMPDB_ID numeric(10) IDENTITY PRIMARY KEY'
EXECUTE (@SqlStr)
--计算临时表中的记录数
--SELECT @SqlStr = 'SELECT Count(*) From tempdb..Paging'+@dt
--EXECUTE (@SqlStr)
--选取记录号在起始行和结束行中间的记录
SELECT @SqlStr = 'SELECT * FROM tempdb..Paging'+@dt+' WHERE TEMPDB_ID > '+convert(varchar,@FirstRec)+' and TEMPDB_ID < '+convert(varchar,@LastRec)
EXECUTE (@SqlStr)
--删除临时表
SELECT @SqlStr = 'DROP TABLE tempdb..Paging'+@dt
EXECUTE (@SqlStr)
END
/*
some comments:
1. @SqlStr varchar(8000), depends on your page size
2. this is a generic paging sp, if you just want to use it for specific table,
you'd better change the 'tempdb..Paging' to #Paging, the performance will be better
*/
文章转自:http://hi.baidu.com/liliangwen/blog/item/bdd0c2117c65eb10b8127b3b.html