使用sql server的高手幫忙看一下，該存儲過程有什麼問題.

[richardwong]

這個存儲過程，在sql server中執行能正常完成。
但用pb調用，就不能完成了，我將依據結果指出中斷的地點。該存儲過程是
會計初始化數據啟用過帳，從ac_init將數據累加到acperamt.
先貼pb的調用代碼。
Integer li_Return = 99
String ls_AcctCode = ''

gc_userconnect.uf_begintransaction()

Declare lmsp_StartAC Procedure For msp_StartAC
@ti_Return = :li_Return Output, @ts_AcctCode = :ls_AcctCode Output Using gc_userconnect ;
Execute lmsp_StartAC ;
Fetch lmsp_StartAC Into :li_Return, :ls_AcctCode ;
Close lmsp_StartAC ;

If gc_userconnect.SqlCode = 0 Then
If li_Return = 0 Then
Commit Using gc_userconnect ;
Messagebox(gs_msgtitle, uf_CaseLanguage("Account System is started !","會計系統已經啟用 !","",""), Exclamation!)

Close(Parent)
Else
Rollback Using gc_userconnect ;

Choose Case li_Return
Case 1
Messagebox(gs_msgtitle, uf_CaseLanguage("Total Debit Amount is not equal to Total Credit Amount !","借方總金額不等於貸方總金額 !","",""), Exclamation!)
Case 2
Messagebox(gs_msgtitle, uf_CaseLanguage("Account " + ls_AcctCode + " is not found !","帳項" + ls_AcctCode + " 沒有發現!","",""), Exclamation!)
Case 3
Messagebox(gs_msgtitle, uf_CaseLanguage("Account " + ls_AcctCode + " is not detail !","帳項" + ls_AcctCode + " 不是明細帳項!","",""), Exclamation!)
Case Else
Messagebox(gs_msgtitle, uf_CaseLanguage("Account Start happen error !","會計啟用出現錯誤 !","",""), Exclamation!)
End Choose
End If
Else
Rollback Using gc_userconnect ;

Messagebox(gs_msgtitle, uf_CaseLanguage("Account Start happen error!","會計啟用出現錯誤!","",""), Exclamation!)
End If

下面是存儲過程msp_startac的代碼：(返回0為正常執行完畢)
CREATE PROCEDURE dbo.msp_StartAc
( @ti_Return Integer = 99 Output,
@ts_AcctCode Varchar(10) = '' Output
)
AS

Declare @ld_SDAmt Decimal(18,2), @ld_SCAmt Decimal(18,2), @ld_ADAmt Decimal(18,2), @ld_ACAmt Decimal(18,2)
Set @ld_SDAmt = 0
Set @ld_SCAmt = 0
Set @ld_ADAmt = 0
Set @ld_ACAmt = 0

Select @ld_SDAmt = Sum(Open_CDAmt), @ld_SCAmt = Sum(Open_CBAmt),
@ld_ADAmt = Sum(Accu_DbAmt), @ld_ACAmt = Sum(Accu_CBAmt)
From Ac_Init
If @ld_SDAmt <> @ld_SCAmt Or @ld_ADAmt <> @ld_ACAmt
Begin
Set @ti_Return = 1
Return
End

Declare @ld_OpenDate DateTime,@li_StartMonth Integer,@li_Year Integer, @ls_Period Varchar(6)
Declare @ls_AccuPeriod Varchar(6)

Select @ld_OpenDate = Open_Date,@li_StartMonth = StartMonth From Ac_Sys Where Recno = 1

If Month(@ld_OpenDate) >= @li_StartMonth Set @li_Year = Year(@ld_OpenDate)
Else Set @li_Year = Year(@ld_OpenDate) - 1

Set @ls_Period = Str(@li_Year, 4) + '00'

If Month(@ld_OpenDate) >= @li_StartMonth Set @ls_AccuPeriod = Str(@li_Year, 4) + Right('0' + Rtrim(Ltrim(Str(Month(@ld_OpenDate) - @li_StartMonth + 1, 2))), 2)
Else Set @ls_AccuPeriod = Str(@li_Year, 4) + Right('0' + Ltrim(Rtrim(Str(Month(@ld_OpenDate) - @li_StartMonth + 13, 2))), 2)

Delete From Acperamt Where Period <= @ls_AccuPeriod
If @@Error <> 0 Return

Declare @ld_SDBAmt Decimal(18, 2), @ld_SCBAmt Decimal(18, 2), @ld_ADBAmt Decimal(18, 2), @ld_ACBAmt Decimal(18, 2)
Declare @ls_AcctCode Varchar(10), @li_Detail Int, @ls_ParentCode Varchar(10)
Declare @li_FetchStatus Int

Declare lcur_Acinit Cursor For
Select Acct_Code, Open_CAmt, Open_CBAmt, Open_DAmt, Open_CDAmt, Accu_CAmt, Accu_CBAmt, Accu_Damt,
Accu_DBAmt From Ac_Init
Open lcur_Acinit

Fetch Next From lcur_Acinit Into @ls_AcctCode, @ld_SCAmt, @ld_SCBAmt, @ld_SDAmt, @ld_SDBAmt, @ld_ACAmt,
@ld_ACBAmt, @ld_ADAmt, @ld_ADBAmt
Set @li_FetchStatus = @@Fetch_Status

While @li_FetchStatus = 0
Begin
Select @li_Detail = Detail, @ls_ParentCode = ParentCode From Account Where Acct_Code = @ls_AcctCode
If @@RowCount = 0
Begin
Set @ts_AcctCode = @ls_AcctCode
Set @ti_Return = 2
Break
End
If @li_Detail <> 1
Begin
Set @ts_AcctCode = @ls_AcctCode
Set @ti_Return = 3
Break
End

Insert Into Acperamt (Acct_Code, Period, dr_amt, cr_amt, b_dr_amt, b_cr_amt)
Values (@ls_AcctCode, @ls_Period, @ld_SDAmt, @ld_SCAmt, @ld_SDBAmt, @ld_SCBAmt)
If @@Error <> 0 Break


Insert Into Acperamt (Acct_Code, Period, dr_amt, cr_amt, b_dr_amt, b_cr_amt)
Values (@ls_AcctCode, @ls_AccuPeriod ,@ld_ADAmt,@ld_ACAmt,@ld_ADBAmt,@ld_ACBAmt) ---當第二次執行到這裡好像就中斷了。
If @@Error <> 0 Break

While Len(@ls_ParentCode) > 0
Begin
Set @ls_AcctCode = @ls_ParentCode
Set @ls_ParentCode = ''
Select @ls_ParentCode = ParentCode From Account Where Acct_Code = @ls_AcctCode

Select * From Acperamt Where Acct_Code = @ls_AcctCode And Period = @ls_Period
If @@RowCount = 0
Begin
Insert Into Acperamt (Acct_Code, Period, dr_amt, cr_amt, b_dr_amt, b_cr_amt)
Values (@ls_AcctCode, @ls_Period, @ld_SDAmt, @ld_SCAmt, @ld_SDBAmt, @ld_SCBAmt)
If @@Error <> 0
Begin
Set @ti_Return = 98
Break
End
End
Else
Begin
Update Acperamt Set Dr_Amt = Dr_Amt + @ld_SDAmt, Cr_Amt = Cr_Amt + @ld_SCAmt,
b_Dr_Amt = b_dr_Amt + @ld_SDBAmt, b_cr_amt = b_cr_amt + @ld_SCBAmt
Where Acct_Code = @ls_AcctCode And Period = @ls_Period
If @@Error <> 0
Begin
Set @ti_Return = 98
Break
End
End

Select * From Acperamt Where Acct_Code = @ls_AcctCode And Period = @ls_AccuPeriod
If @@RowCount = 0
Begin
Insert Into Acperamt (Acct_Code, Period, dr_amt, cr_amt, b_dr_amt, b_cr_amt)
Values (@ls_AcctCode, @ls_AccuPeriod, @ld_ADAmt, @ld_ACAmt, @ld_ADBAmt, @ld_ACBAmt)
If @@Error <> 0
Begin
Set @ti_Return = 98
Break
End
End
Else
Begin
Update Acperamt Set Dr_Amt = Dr_Amt + @ld_ADAmt, Cr_Amt = Cr_Amt + @ld_ACAmt,
b_Dr_Amt = b_dr_Amt + @ld_ADBAmt, b_cr_amt = b_cr_amt + @ld_ACBAmt
Where Acct_Code = @ls_AcctCode And Period = @ls_AccuPeriod
If @@Error <> 0
Begin
Set @ti_Return = 98
Break
End
End
End

If @ti_Return = 98 Break

Fetch Next From lcur_Acinit Into @ls_AcctCode, @ld_SCAmt, @ld_SCBAmt, @ld_SDAmt, @ld_SDBAmt, @ld_ACAmt,
@ld_ACBAmt, @ld_ADAmt, @ld_ADBAmt
Set @li_FetchStatus = @@Fetch_Status
End

Close lcur_Acinit
Deallocate lcur_Acinit

If @ti_Return = 98 Or @ti_Return = 99 Return
Else
Begin
Update Ac_Sys Set Started = 1, Curr_Year = @li_Year Where Recno = 1
If @@Rowcount = 0
Begin
Set @ti_Return = 99
Return
End
End

Set @ti_Return = 0

Return
GO

[mah1212]

在pb中不正常是指什么？sqlcacode<>0？还是得不到返回值？说清楚点！看这么长代码累！

[richardwong]

pb中不正常，是指sqlcode=0但是存儲過程的返回值li_Return 不為零。li_Return似乎是
初始值，在調用存儲過程前，它是什麼值，此時就是什麼值。
從結果看存儲過程執行中斷了，中斷在我指出的地方。
這會不會有執行超時造成的原因？這個存儲過程我在sql server中用了5,6秒才執行完成的。

[richardwong]

還有個奇怪的現象給大家說一說。
在sql server中表acperamt會被增加485條記錄。
我在pb中將Integer li_Return = 99
改為Integer li_Return = 0, 這時數據會被提交。
程序我反復執行幾次，發現acperamt表中被添加的記錄數是變動的。
有時是4條，有時是5條。
奇怪啊。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。

[mah1212]

存储过程有问题，存储过程中涉及的表我不知道，也不清楚要实现什么功能。

[zhouningjfcxs]

sqlcode=0 or sqlcode=100 都是正常