＋设为首页 ＋添加到收藏夹
利用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, NIsUserTable) = 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;
//
修改：
select @var2=str(convert(dec,1)+1,4)
select @v1=charindex( ,@var2)
while @v1>0 and @v1 < 4
begin
select @var2=stuff(@var2,@v1,1,0)
select @v1=charindex( ,@var2)
end
sql语句的问题
