快下班了,没时间认真看你的SQL代码,给你一个完整的,不过我用的是存储过程,应该可以实现你想要的,参考一下吧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
GOnumber表结构
sNo Char(12)
sTableName Varchar(50)
ab Varchar(20)
@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
GOnumber表结构
sNo Char(12)
sTableName Varchar(50)
ab Varchar(20)
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货