Sybaseページング・ストレージ・プロシージャ
行単位の読み取り
ページ単位の読み取り
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