主键维护，获取和更新最大的ID

IDTable表结构：

 

 4 
 5 ALTER PROC [dbo].[P_GetUpdateMaxIDs]
 6     @IDType BIGINT,
 7     @Count INT --需要获得几个
 8 AS
 9 BEGIN
10 
11     IF EXISTS(SELECT * FROM tempdb.sys.tables where OBJECT_ID=object_id('tempdb.sys.#TEMPMAXIDS'))
12     BEGIN
13         DROP TABLE #TEMPMAXIDS
14     END
15 
16     DECLARE @Now DATETIME=GETDATE(),
17             @TempValue VARCHAR(20),
18             @MaxValue VARCHAR(20),
19             @NewValue BIGINT,
20             @YEAR VARCHAR(4),
21             @MONTH VARCHAR(2),
22             @DAY VARCHAR(2)
23     
24     SET @MaxValue=ISNULL((SELECT MaxValue FROM dbo.IDTable WHERE ID=@IDType),'')
25     
26     SELECT @YEAR=YEAR(@NOW),@MONTH=MONTH(@NOW),@DAY=DAY(@NOW)
27     SET @TempValue=CAST(@YEAR AS CHAR(4))
28         +(case when @MONTH<10 then '0'+CAST(@MONTH AS CHAR(1)) else CAST(@MONTH AS CHAR(2)) end)
29         +(case when @DAY<10 then '0'+CAST(@DAY AS CHAR(1)) else CAST(@DAY AS CHAR(2)) end)
30         +'00000000';
31         
32     IF(LEN(@MaxValue)>15)
33     BEGIN
34 
35         SET @YEAR=(select substring(@MaxValue,0,5))
36         SET @MONTH=(select substring(@MaxValue,5,2))
37         SET @DAY=(select substring(@MaxValue,7,2))
38         
39         --判断是否是今天
40         IF(CONVERT(VARCHAR(10),@Now,120)=(@YEAR+'-'+@MONTH+'-'+@DAY))
41         BEGIN
42             SET @TempValue=@MaxValue
43         END
44     
45     END
46     
47     SET @NewValue=CAST(@TempValue AS BIGINT)
48     
49     CREATE TABLE #TEMPMAXIDS(Val BIGINT)
50     
51     WHILE (@Count>0)
52     BEGIN
53         SET @NewValue=@NewValue+1
54         INSERT INTO #TEMPMAXIDS(Val) VALUES(@NewValue)
55         SET @Count=@Count-1
56     END
57     
58     UPDATE dbo.IDTable SET MaxValue=@NewValue WHERE ID=@IDType
59     
60     SELECT Val FROM #TEMPMAXIDS
61 
62 END

 

得到的主键如：2017083000000001

按日期的规则如此增量上去，每天都会从00000001开始。

 

转载于:https://www.cnblogs.com/shousiji/p/7451697.html