22,210
社区成员
发帖
与我相关
我的任务
分享
Create table AUTOID(ID int identity primary key,Date datetime,NextNumber int)
go
CREATE PROCEDURE [dbo].[GetNumber]
@length INT,
@currdate datetime,
@RegularVal NVARCHAR(50) output
AS
BEGIN TRAN T1
declare @NextNumber int
select @NextNumber=NextNumber from AUTOID with(rowlock,holdlock) where datediff(d,Date,@currdate)=0
if @NextNumber is null
begin
set @NextNumber=1
insert into AUTOID values(@currdate,2)
end
else
begin
update AUTOID with(rowlock,holdlock) set @NextNumber=NextNumber=NextNumber+1 where datediff(d,Date,@currdate)=0
end
SET @RegularVal=convert(varchar(8),@currdate,112) + RIGHT(REPLICATE(0,@length) + rtrim(@NextNumber), @length)
COMMIT TRAN T1
go
declare @RegularVal NVARCHAR(50)
exec [GetNumber] @length=5,@currdate='20111130',@RegularVal=@RegularVal output
CREATE PROCEDURE [dbo].[GetNumber]
@length INT,
@currdate NVARCHAR(8),
@RegularVal NVARCHAR(50) output
AS
BEGIN TRAN T1
DECLARE @CurrentValue INT
INSERT INTO AUTOID (CreateTime) VALUES(Getdate())-- 种子表名这个表就两个字段,一个自增字段,一个是时间
SET @CurrentValue=@@Identity
SELECT * FROM AUTOID WITH (TABLOCKX)
waitfor delay '00:00:05'
SET @RegularVal=left(@currdate,6) + RIGHT(REPLICATE(0,@length) + CAST((@CurrentValue) as NVARCHAR), @length)
COMMIT TRAN T1
CREATE PROCEDURE [dbo].[GetNumber]
@length INT,
@currdate NVARCHAR(8),
@RegularVal NVARCHAR(50) output
AS
BEGIN TRAN T1
DECLARE @CurrentValue INT
INSERT INTO AUTOID (CreateTime) VALUES(Getdate())-- 种子表名这个表就两个字段,一个自增字段,一个是时间
SET @CurrentValue=@@Identity
SELECT * FROM AUTOID WITH (TABLOCKX)
waitfor delay '00:00:05'
SET @RegularVal=convert(varchar(8),@currdate,112) + RIGHT(REPLICATE(0,@length) + CAST((@CurrentValue) as NVARCHAR), @length)
COMMIT TRAN T1
你的日期转换为什么是这样的??
可以直接
convert(varchar(10) , 时间字段, 112 )+right(rtrim('000000'+ltrim(id)),6)就可以实现了
怎么写得这么麻烦?
declare @r nvarchar(50)
exec getnumber 18,'20111230',@r output
select @r