存储过程在PB中无法正常执行?(SQL SERVER + PB10)
存储过程在查询分析器中执行正常,在PB中只执行一半
存储过程如下:
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'sp_Get_PLNo' AND type = 'P')
DROP PROCEDURE sp_Get_PLNo
GO
CREATE PROCEDURE sp_Get_PLNo @Country_Code Char(2), @PLNo Char(11) OUTPUT
as
BEGIN transaction tranpl
Declare @CurrentDate Datetime;
Declare @CurrentYear Char(2);
Declare @Increment int, @Next_No Varchar(7), @Next_No_New Varchar(7)
BEGIN
Select @CurrentDate = Getdate();
Select @CurrentYear = Right(Cast(Year(@CurrentDate) As Char(4)),2);
Select @Increment = Increment, @Next_No = Ltrim(Rtrim(next_no)) From RN_M
Where Country_Code = @Country_Code And prefix_type = 'PL'
Select @Next_No_New = Ltrim(Rtrim(Right('000000' + Cast((Cast(@Next_No As int) + @Increment) As Varchar(7)), 7)))
Update RN_M Set next_no = @Next_No_New
Where Country_Code = @Country_Code And prefix_type = 'PL'
If @@Error <> 0
Begin
Select @PLNo = 'Error'
Return
End
Select @PLNo = 'PL' + @CurrentYear + @Next_No
END
--END
commit transaction tranpl
功能是取一个流水号,然后把当前号码加1再保存到数据库
PB调用代码如下:
declare proc_sp_Get_PLNo procedure for sp_Get_PLNo
@Country_Code = :gs_country_code,
@PLNo = :ls_pl_no OUTPUT
using sqlca2;
execute proc_sp_Get_PLNo;
if sqlca2.sqlcode < 0 then
ls_err = sqlca2.sqlerrtext
messagebox(f_getmsgtext('DlgInfTitle'), ls_err)
return -1
end if
fetch proc_sp_Get_PLNo into :ls_pl_no;
close proc_sp_Get_PLNo;
Rollback Using SQLCA2;
问题是在查询分析器中执行一切正常,但在PB调用时只能取到流水号,但没有Update数据库,导致每次取到的号码都是一样的。
请问各位大虾问题出在哪里?