进销存系统单号算号器

进销存系统单号算号器
1.       单号存储表 DLL 的 SQLCODE
2.       存储过程的 sybase/mssql2005 代码实现
3.       调用存储过程代码
// 单号表
create table dbo.t_get_billno
(
number_type char(1) not null,
billno char(10) not null,
owner_name char(10) not null,
create_date datetime not null,
flag char(1) not null
)
alter table dbo.t_get_billno
add constraint t_get_billno_px primary key nonclustered (number_type,billno)

//billno creater
drop PROCEDURE dbo.get_mu_billno;
CREATE PROCEDURE dbo.get_mu_billno(
    @number_type char(1),  
    @owner_name char(10),  
    @first_number char(9) output  
)
AS   
declare @i int    
declare @ctempno char(9)   
declare @ctemp1 char(6)    
declare @ctemp2 char(1)    
declare @itemp3 int

begin    

        select @ctemp1=substring(CONVERT(varchar(30),getdate(),101),9,2)+substring(CONVERT(varchar(30),getdate(),101),1,2)+substring(CONVERT(varchar(30),getdate(),101),4,2)
        select @ctempno=max(billno)  
        from t_get_billno  
        tablockx  
        where  (number_type=@number_type) and (substring(billno,2,6) =@ctemp1)
 
        if (@ctempno=NULL)  
            begin  
                  select @itemp3=0
             end  
        else  
            begin  
                select @itemp3=convert(int,substring(@ctempno,8,2))  
            end           
        if (@itemp3=99)  
            begin  
                  return 3         
            end  
        else  
            begin  
                select @itemp3=@itemp3+1  
            end  
        select  @ctempno=@number_type+@ctemp1+replicate('0',(2-datalength(ltrim(str(@itemp3,2,0)))))+ltrim(str(@itemp3,2,0))
        insert into t_get_billno values(@number_type,@ctempno,@owner_name,getdate(),'0')       
        select @first_number=@ctempno

        return 0
end


//call procedure with pb
string number_type,owner_name,getno
DECLARE p_get_billno PROCEDURE FOR dbo.get_mu_billno
@number_type =:number_type,
@owner_name=:owner_name,
@first_number = :getno output;
number_type=sle_ctype.text
owner_name=g_username
execute p_get_billno;
fetch p_get_billno into :getno;
close p_get_billno;
commit;
if getno='' then
    messagebox('Warning!','check data Error !')
else
        dw_1.setredraw(false)
        dw_1.retrieve(number_type)
        dw_1.setredraw(true)
        st_row.text=string(dw_1.rowcount())
end if