關於數據庫光標的一些問題,向各位大蝦請教

smzdg 2004-11-25 03:53:46

小弟有如下存儲過程,在PB開發環境下運行可以得到正確結果,但一到運行環境中就沒有結果了,也不報任何錯誤信息,請大蝦們指教:

這個存儲過程的目的是通過光標的從數據表中取出指寫個數的記錄,即分頁取數方式!
起先我將結果Fetch出來,做成數據窗口,開發環境可以,運行環境取不出記錄.
然後我將記錄Fetch到一個臨時表中,從臨時表中輸出記錄,也是開發環境可以,運行環境取不出記錄.
再然後我建立一個正式表,將記錄Fetch到正式表中,在PB中執行此存儲過程,結果也不能返回結果.
後來我又試過用動態SQL語句執行此存儲過程,仍然不行.

我實在是沒有辦法了,不知道到底是什麽原因

create PROCEDURE p_iqc_check_mova
@vendno char(10) ,
@pno char(24),
@pageNo int = 1,
@row_in_page int = 20
as
Declare @first int

begin

delete from iqc_check_tmp

select @first =(@pageNo-1)*@row_in_page
if(@first < 0) select @first = 0

declare check_record cursor for
select seq_no,in_date,qty,check_mode,iqcno,iqc_result,other,remark1,remark2,ent_by,ent_date
from dbo.iqc_check
where vendno=@vendno and
pno=@pno
order by in_date Desc,seq_no Desc

open check_record
set cursor rows 1 for check_record

Declare @seq_no int,@in_date datetime,@qty float,@check_mode char(1),
@iqcno char(10),@iqc_result char(40),@other char(20),
@remark1 char(80),@remark2 char(80),@ent_by char(10),@ent_date datetime
declare @i int
declare @j int
select @i = 0
SELECT @j = 1

while(@@sqlstatus = 0)
begin
if(@first = @i)
begin
while (@j <= @row_in_page )
begin
FETCH check_record
INTO @seq_no,
@in_date,
@qty,
@check_mode,
@iqcno,
@iqc_result,
@other,
@remark1,
@remark2,
@ent_by,
@ent_date

IF (@@sqlstatus <>0 )
break

INSERT INTO iqc_check_tmp
values( @seq_no,
@in_date,
@qty,
@check_mode,
@iqcno,
@iqc_result,
@other,
@remark1,
@remark2,
@ent_by,
@ent_date)

SELECT @j=@j+1

END
break
END

FETCH check_record
INTO @seq_no,
@in_date,
@qty,
@check_mode,
@iqcno,
@iqc_result,
@other,
@remark1,
@remark2,
@ent_by,
@ent_date

select @i = @i + 1
end

close check_record
deallocate cursor check_record

end
;

...全文