27,580
社区成员
发帖
与我相关
我的任务
分享
create table SMAUTO_1
(
ACOMPANY VARCHAR(50) NOT NULL,
AID VARCHAR(50) NOT NULL UNIQUE,
ADESC VARCHAR(50) NULL,
ADESCCHS VARCHAR(50) NULL,
APREFIX VARCHAR(50) NULL,
ADATE VARCHAR(50) NULL,
ALENGTH INT NULL,
ANEXTNO INT NULL,
ALASTDATE VARCHAR(50) NULL,
VGUID UNIQUEIDENTIFIER NOT NULL,
VCRTTIME DATETIME NULL,
VCRTUSER VARCHAR(50) NULL,
VMDTIME DATETIME NULL,
VMDUSER VARCHAR(50) NULL,
VSTATUS VARCHAR(50) NULL,
VLOCK VARCHAR(50) NULL,
VLOCKTIME DATETIME NULL,
)
DELETE FROM SMAUTO_1
INSERT INTO SMAUTO_1(ACOMPANY,AID,APREFIX,ADATE,ALENGTH,VGUID) VALUES('LANDA','0001','BN','YYMM',4,NEWID())
select * from SMAUTO_1
--创建存储过程
IF OBJECT_ID('SP_AUTONO','P') IS NOT NULL DROP PROCEDURE SP_AUTONO
GO
CREATE PROCEDURE SP_AUTONO
@AID VARCHAR(20),
@AUTONO VARCHAR(30) OUTPUT
AS
BEGIN
DECLARE @ADATE VARCHAR(20),
@ALENGTH INT ,
@ANEXTNO INT,
@ALASTTADE VARCHAR(8)
SELECT
@AUTONO=ISNULL(APREFIX,''),
@ADATE=ADATE,
@ALENGTH=ALENGTH,
@ANEXTNO=ANEXTNO,
@ALASTTADE=ALASTDATE
FROM SMAUTO_1
WHERE AID=@AID
DECLARE @DATEPART VARCHAR(10)
SET @DATEPART=CONVERT(VARCHAR(10),GETDATE(),120)
SET @DATEPART=CASE @ADATE
WHEN 'YYMM' THEN SUBSTRING(@DATEPART,3,2)+SUBSTRING(@DATEPART,6,2)
WHEN 'YYYYMM' THEN SUBSTRING(@DATEPART,1,4)+SUBSTRING(@DATEPART,6,2)
WHEN 'YY' THEN SUBSTRING(@DATEPART,3,2)
WHEN 'YYYY' THEN SUBSTRING(@DATEPART,1,4)
WHEN 'YYMMDD' THEN SUBSTRING(@DATEPART,3,2)+SUBSTRING(@DATEPART,6,2)+RIGHT(@DATEPART,2)
WHEN 'YYYYMMDD' THEN SUBSTRING(@DATEPART,1,4)+SUBSTRING(@DATEPART,6,2)+RIGHT(@DATEPART,2)
ELSE ''
END
SET @AUTONO=@AUTONO+@DATEPART
DECLARE @NEXTNO VARCHAR(30)
IF(@DATEPART='' OR @DATEPART=@ALASTTADE)
BEGIN
SET @NEXTNO=CONVERT(VARCHAR(30),@ANEXTNO)
SET @NEXTNO=REPLICATE('0',@ALENGTH-LEN(@NEXTNO))+@NEXTNO
UPDATE SMAUTO_1 SET ANEXTNO=ANEXTNO+1 WHERE AID=@AID
END
ELSE
BEGIN
SET @NEXTNO=REPLICATE('0',@ALENGTH-1)+'1'
UPDATE SMAUTO_1 SET ANEXTNO=2,ALASTDATE=@DATEPART WHERE AID=@AID
END
SET @AUTONO=@AUTONO+@NEXTNO
END
GO
--调用存储过程
DECLARE @V_OUTPUT VARCHAR(64)
EXEC SP_AUTONO '0001',@V_OUTPUT OUTPUT
SELECT @V_OUTPUT
IF NOT EXISTS(SELECT 1 FROM SMAUTO_1 WHERE 公司缩写码 = 'XXX' AND AID = 'YYY')