|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-09-13 : 21:37:04
|
dowie writes "could you give me an exanmple stored procedure that used to paging a recordset.
regards
dowie
note :
i have wrote a sp in mssql. this sp is designed to display a recordset with paging capability.for example i used this sp to display page 3rd of 15 with 10 records/page.the problem is my sp not always work properly.i must recompile it again (save sp in enterprise manager - not make anything changed) so it can work properly again. can you help me where the mistake is, in my sp or in my ms sql server setting.
below my sp
CREATE PROCEDURE sp_listKereta (
@barisperhalaman int,
@halaman int,
@KotaBerangkate varchar(30),
@KotaTibae varchar(30),
@kodehari tinyint
)
AS
Set nocount on
Declare @recordpertama int,@jumlahbaris int
Declare @NamaKereta varchar(30),@StasiunBerangkat varchar(30),@KotaBerangkat varchar(30),@jamberangkat varchar(11),@StasiunTiba varchar(30),@KotaTiba varchar(30),@jamtiba varchar(11),@hari varchar(6)
select @recordpertama = (@halaman-1)*@barisperhalaman + 1
select @jumlahbaris = 0
declare rsnya scroll cursor
for SELECT NamaKereta ,StasiunBerangkat ,KotaBerangkat,jamberangkat,StasiunTiba ,KotaTiba,jamtiba,hari FROM Kereta Where KotaBerangkat = @KotaBerangkate and KotaTiba = @KotaTibae and kodehari=@kodehari order by jamberangkat
Create Table #hasil (NamaKereta varchar(30),StasiunBerangkat varchar(30),KotaBerangkat varchar(30),jamberangkat varchar(11),StasiunTiba varchar(30),KotaTiba varchar(30),jamtiba varchar(11),hari varchar(6))
open rsnya
fetch absolute @recordpertama from rsnya into @NamaKereta,@StasiunBerangkat,@KotaBerangkat,@jamberangkat,@StasiunTiba,@KotaTiba,@jamtiba,@hari
while @@FETCH_STATUS=0
begin
insert into #hasil values (@NamaKereta,@StasiunBerangkat,@KotaBerangkat,@jamberangkat,@StasiunTiba,@KotaTiba,@jamtiba,@hari)
select @jumlahbaris = @jumlahbaris + 1
if @jumlahbaris=@barisperhalaman break
fetch next from rsnya into @NamaKereta,@StasiunBerangkat,@KotaBerangkat,@jamberangkat,@StasiunTiba,@KotaTiba,@jamtiba,@hari
end
close rsnya
deallocate rsnya
select * from #hasil
drop table #hasil
GO" |
|
paging recordset with stored procedure