27,579
社区成员
发帖
与我相关
我的任务
分享
CREATE trigger [dbo].[T_PartValuation_AfterInsert]
on [dbo].[PartValuation]
After Insert
as
--在添加数据后生成流水号并update
declare @ValuationCode nvarchar(50),@ValuationID int
set @ValuationID=@@IDENTITY
exec P_CustomerInquiry_CreateCode @ValuationCode out
update PartValuation set ValuationCode=@ValuationCode where ValuationID=@ValuationID
CREATE PROCEDURE [dbo].[P_PartValuation_CreateCode] @ValuationCode nvarchar(50) output
as
--对客户估价表进行生成流水号
declare @count int,@num int
--先生成例如:20110419 的前半部分
set @ValuationCode = convert(nvarchar(50),getdate(),112)
--获取今天已经生成多少个类似这样的(这里主要防止未生成)
select @count=count(*) from dbo.PartValuation where ValuationCode like (@ValuationCode+'%')
--如果尚未生成,则设置编号为0,否则获取最大的编号
if (@count = 0)
begin
set @num = 0
end
else
begin
select @num=cast(substring(MAX(ValuationCode),9,4) as int) from dbo.PartValuation where ValuationCode like (@ValuationCode+'%')
end
--在最大的编号的基础上+1
set @num = @num + 1
--这里我主要是想生成四位的编号并且与之前生成的前半部分合并,例如201104190001等
if (@num<10)
begin
set @ValuationCode = @ValuationCode + '000' + cast(@num as nvarchar(20))
end
else if (@num < 100)
begin
set @ValuationCode = @ValuationCode + '00' + cast(@num as nvarchar(20))
end
else if (@num < 1000)
begin
set @ValuationCode = @ValuationCode + '0' + cast(@num as nvarchar(20))
end
else
begin
set @ValuationCode = @ValuationCode + cast(@num as nvarchar(20))
end
LZ我帮你简化一下存储过程吧
CREATE PROCEDURE [dbo].[P_PartValuation_CreateCode] @ValuationCode nvarchar(50) output
as
--对客户估价表进行生成流水号
declare @num int
--先生成例如:20110419 的前半部分
set @ValuationCode = convert(nvarchar(50),getdate(),112)
--获取今天已经生成多少个类似这样的(这里主要防止未生成)
select @num=isnull(MAX(cast(substring(ValuationCode,9,4) as int)),0)+1 from dbo.PartValuation where ValuationCode like (@ValuationCode+'%')
set @ValuationCode = @ValuationCode + right('000' + cast(@num as nvarchar(20)),4)
end
这步错了,应该最后再用MAx
select @num=MAX(cast(substring(ValuationCode,9,4) as int)) from dbo.PartValuation where ValuationCode like (@ValuationCode+'%')