一个自动取得下一个单号的存储过程的例子
ilang 2002-08-22 06:06:15 CREATE PROCEDURE GetNo
@No varchar(20) output ,
@TableName varchar(50),
@Flag int--是否写入新单号
AS
declare @Year Int,
@Month int,
-- @Day int,
@Temp_No varchar(10),
@RandNo varchar(4),
@No1 varchar(10),
@ab Varchar(30)
BEGIN
select @year=Year(GetDate())
select @Month=Month(GetDate())
select @No=Str(@Year,4,0)+
(select
case
when @Month>=10 then Str(@Month,2,0)
when @Month<10 Then '0'+Str(@Month,1,0)
end
)
select @tablename = Rtrim(@tablename)
select @tablename = Ltrim(@tablename)
SELECT @Temp_No = sNo FROM Number where sTableName= @TableName
SELECT @ab = ab FROM Number where sTableName= @TableName
IF @No <> SUBSTRING( @Temp_No,1,6)
begin
SELECT @No1 = @No+'0001'
SELECT @No = @ab+@No+'0001'
end
ELSE
BEGIN
SELECT @RandNo = STR( CONVERT(int,(SUBSTRING( @Temp_No ,7,4) )+1) , 4, 0 )
SELECT @RandNo = REPLACE( @RandNo,' ','0')
SELECT @No = @No + @RandNo
SELECT @No1 = @No
SELECT @No =@ab + @No
END
if @flag=1
UPDATE Number SET sNo = @No1 where sTableName = @TableName
END
GO
number表结构
sNo Char(12)
sTableName Varchar(50)
ab Varchar(20)