You can do 10 fetches. But this will generate 10 result sets ... :
create procedure aa
@rowset integer
as
begin
SET NOCOUNT ON
DECLARE @pos integer, @co int
DECLARE cPartners SCROLL CURSOR FOR SELECT * FROM partners
OPEN cPartners
set @pos = @rowset * 10
set @co = 0
goto FETCHNEXT
while @co < 10 AND @@FETCH_STATUS = 0
begin
set @co = @co + 1
set @pos = @pos + 1
FETCHNEXT:
FETCH ABSOLUTE @pos FROM cPartners -- Will return 1 row to client
end
CLOSE cPartners
DEALLOCATE cPartners
SET NOCOUNT OFF
end
exec aa 2 gives :
i a b
----------- ---------- ----------
20 kaponi VINC
i a b
----------- ---------- ----------
21 mokeri MANU
i a b
----------- ---------- ----------
22 ramelo ARNA
i a b
----------- ---------- ----------
23 retimn PIER
i a b
----------- ---------- ----------
24 brere CLAU
i a b
----------- ---------- ----------
25 karlir JOEL
i a b
----------- ---------- ----------
26 lplus MARI
i a b
----------- ---------- ----------
27 barsa FUAD
i a b
----------- ---------- ----------
28 kafman ALFR
i a b
----------- ---------- ----------
29 dgdevi DIDI
i a b
----------- ---------- ----------
30 drut MICH