建表:
1 USE [JX_IMS_CPK] 2 GO 3 4 SET ANSI_NULLS ON 5 GO 6 7 SET QUOTED_IDENTIFIER ON 8 GO 9 10 CREATE TABLE [dbo].[WCS_Seq]( 11 [SeqType] [int] NOT NULL, 12 [SeqVal] [bigint] NOT NULL, 13 [SeqTime] [datetime] NOT NULL 14 ) ON [PRIMARY] 15 GO 16 17 EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘类型(1-任务单号)‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘WCS_Seq‘, @level2type=N‘COLUMN‘,@level2name=N‘SeqType‘ 18 GO 19 20 EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘序列值‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘WCS_Seq‘, @level2type=N‘COLUMN‘,@level2name=N‘SeqVal‘ 21 GO 22 23 EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘序列时间‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘WCS_Seq‘, @level2type=N‘COLUMN‘,@level2name=N‘SeqTime‘ 24 GO 25 26 EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘序列表‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘WCS_Seq‘ 27 GO
获取序列:
1 USE [JX_IMS_CPK] 2 GO 3 4 SET ANSI_NULLS ON 5 GO 6 SET QUOTED_IDENTIFIER ON 7 GO 8 ALTER PROCEDURE [dbo].[WCS_GetTaskCode] 9 @O_TaskCode VARCHAR(50)=NULL OUTPUT 10 --获取【任务号】 11 AS 12 BEGIN 13 DECLARE @V_SeqType INT=1, --类型 14 @SeqCount INT, 15 @NowTime DATETIME=CAST(GETDATE() AS DATE), 16 @SeqVal BIGINT, 17 @SeqTime DATETIME, 18 @NextVal BIGINT=1; 19 20 SELECT @SeqCount = COUNT(1) 21 FROM WCS_Seq a 22 WHERE a.SeqType=@V_SeqType; 23 24 IF @SeqCount = 0 25 BEGIN 26 INSERT INTO WCS_Seq 27 SELECT @V_SeqType,@NextVal,@NowTime; 28 SET @O_TaskCode=RIGHT(CONVERT(VARCHAR(50),@NowTime,112),6)+RIGHT(‘0000‘+CAST(@NextVal AS VARCHAR(4)),4); 29 SELECT @O_TaskCode; 30 RETURN; 31 END; 32 33 SELECT TOP 1 34 @SeqVal = a.SeqVal , 35 @SeqTime = a.SeqTime 36 FROM WCS_Seq a 37 WHERE a.SeqType=@V_SeqType; 38 39 IF @SeqTime = @NowTime 40 SET @NextVal = @SeqVal + 1; 41 ELSE 42 SET @NextVal = 1; 43 44 UPDATE a 45 SET a.SeqVal = @NextVal , 46 a.SeqTime = @NowTime 47 FROM WCS_Seq a 48 WHERE a.SeqVal = @SeqVal 49 AND a.SeqType=@V_SeqType; 50 51 SET @O_TaskCode=RIGHT(CONVERT(VARCHAR(50),@NowTime,112),6)+RIGHT(‘0000‘+CAST(@NextVal AS VARCHAR(4)),4); 52 END;
使用方法:
1 USE [JX_IMS_CPK] 2 GO 3 4 DECLARE @return_value int, 5 @O_TaskCode varchar(50) 6 7 EXEC @return_value = [dbo].[WCS_GetTaskCode] 8 @O_TaskCode = @O_TaskCode OUTPUT 9 10 SELECT @O_TaskCode as N‘@O_TaskCode‘ 11 12 SELECT ‘Return Value‘ = @return_value 13 14 GO
结果:
2004080008
0