110,538
社区成员
发帖
与我相关
我的任务
分享
CREATE procedure [dbo].[GetSNO](@TABLENAME VARCHAR(50), @SNO CHAR(4) output) as
begin
DECLARE @MM CHAR(4),@NO INT
SET @MM = SUBSTRING(convert(CHAR(8), getdate(), 112), 3,4);
begin tran
if not exists(select 1 from T_SYS_COUNT where TABLENAME = @TABLENAME)
begin
insert into T_SYS_COUNT(TABLENAME,COUNT,MM)
values(@TABLENAME,1,@MM)
set @SNO=1
end
else if not exists(select 1 from T_SYS_COUNT where TABLENAME = @TABLENAME AND MM = @MM)
begin
DELETE FROM T_SYS_COUNT WHERE TABLENAME = @TABLENAME;
insert into T_SYS_COUNT(TABLENAME,COUNT,MM)
values(@TABLENAME,1,@MM)
set @SNO=1
end
else
begin
UPDATE T_SYS_COUNT set @NO = COUNT = (COUNT+1)%10000 where TABLENAME = @TABLENAME;
SET @SNO=RIGHT('000'+@NO,4);
end
commit tran
end
GO
DECLARE @SNO CHAR(4)
EXEC GetSNO 'A',@SNO OUT
PRINT @SNO