你也会遇到的问题吧，你也会遇到的问题吧，你也会遇到的问题吧

btlxy 2003-09-16 05:34:46

利用pb调用存贮过程，实现建立单据号AyyyymmddXXXX,格式：字母+yyyy+mm+dd+xxxx,
其中字母：A,B,C等
yyyy(为年份)，mm(月),dd（日期）
xxxx(为顺序号),0001,0002,0003,....

表结构：
if exists (select * from sysobjects where id = object_id(N'[dbo].[Djh_wh]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Djh_wh]
GO

CREATE TABLE [dbo].[Djh_wh] (
[id] [decimal](18, 0) IDENTITY (1, 1) NOT NULL ,
[Djh] [char] (13) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Djh_wh] WITH NOCHECK ADD
CONSTRAINT [PK_Djh_wh] PRIMARY KEY NONCLUSTERED
(
[id]
) ON [PRIMARY]
GO

如下的过程：

CREATE PROCEDURE p_djh
@ls_sign char(1),
@ret_value char(13) output
AS

Declare @ls_max numeric
Declare @ls_max1 numeric
Declare @ls_djh char(13)
declare @ls_vocation char(4)
declare @v1 int
Declare @var2 char(13)

Begin transaction
---
if exists(select * from djh_wh where substring(djh,1,1)=@ls_sign)-----存在赠修改信息
begin
select @ls_djh=(select SUBSTRING(djh,10,4) from djh_wh where substring(djh,1,1)=@ls_sign and substring(djh,2,8)=convert(char(8),getdate(),112))
-- if (@ls_djh is null) or(rtrim(ltrim(@ls_djh))='') select @ls_djh='0001'
-- else
begin

-- Print('Ok ,pass1')

select @var2=str(convert(dec,@ls_djh)+1,4)
select @v1=charindex(' ',@var2)
while @v1>0
begin
select @var2=stuff(@var2,@v1,1,"0")
select @v1=charindex(' ',@var2)
end

select @ls_djh=(@ls_sign+convert(char(8),getdate(),112)+@var2)
Update djh_wh with(rowlock) set djh=@ls_djh where substring(djh,1,1)=@ls_sign
select @ret_value =@ls_djh
end

end
else---不存在则要新增
begin
select @ls_djh=(@ls_sign+convert(char(8),getdate(),112)+'0001')
insert into djh_wh with(rowlock)(djh) values(@ls_djh)
select @ret_value =@ls_djh
end

if @@error<>0
rollback transaction
commit transaction

问题所在：在sql query 调试，一切正常,在pb中执行，首次可以，以后就有问题了，返回数据库的值少位数，比如：A200309160001(首次值正确),A200309160002返回位A20030916，真郁闷！！
在pb:
string ls_xh='',outparm='',result1='',re=''
is_abc=trim(is_abc)////附值为“A”或"B",....

//定义传递参数param1,...
//定义select返回结果集result1,...
//string ls_xh,outparm,result1,re
DECLARE proc_1 PROCEDURE FOR @re = p_djh
@ls_sign = :is_abc ,
@ret_value = :outparm output ;
EXECUTE proc_1;
FETCH proc_1 INTO :result1;
do while sqlca.sqlcode = 0
FETCH proc_1 INTO :result1;
loop
//处理完后再取返回值及输出参数的值
FETCH proc_1 INTO :re, :outparm ;
CLOSE proc_1;
commit;
//

...全文