I have a table in which I am storing ID as VARCHAR(900) this is a primary key column and not using IDENTITY AUTOINCREMENT
I have created the ID as
IE-1-1
It goes on like IE-1-85000 until it reaches the upper limit in both bigint numbers
Now I have around 85000 rows in it
BUT it still shows IE-1-9 as the max value when I query it like this
DECLARE @TempICFEventID TABLE(ID SMALLINT IDENTITY(1,1),SplitValue VARCHAR(MAX))
INSERT INTO @TempICFEventID VALUES ('IE-1-1')
SELECT MAX(SplitValue) FROM @TempICFEventID
INSERT INTO @TempICFEventID VALUES ('IE-1-2')
SELECT MAX(SplitValue) FROM @TempICFEventID
INSERT INTO @TempICFEventID VALUES ('IE-1-3')
SELECT MAX(SplitValue) FROM @TempICFEventID
INSERT INTO @TempICFEventID VALUES ('IE-1-4')
SELECT MAX(SplitValue) FROM @TempICFEventID
INSERT INTO @TempICFEventID VALUES ('IE-1-5')
SELECT MAX(SplitValue) FROM @TempICFEventID
INSERT INTO @TempICFEventID VALUES ('IE-1-6')
SELECT MAX(SplitValue) FROM @TempICFEventID
INSERT INTO @TempICFEventID VALUES ('IE-1-7')
SELECT MAX(SplitValue) FROM @TempICFEventID
INSERT INTO @TempICFEventID VALUES ('IE-1-8')
SELECT MAX(SplitValue) FROM @TempICFEventID
INSERT INTO @TempICFEventID VALUES ('IE-1-9')
SELECT MAX(SplitValue) FROM @TempICFEventID
INSERT INTO @TempICFEventID VALUES ('IE-1-10')
SELECT MAX(SplitValue) FROM @TempICFEventID
INSERT INTO @TempICFEventID VALUES ('IE-1-11')
SELECT MAX(SplitValue) FROM @TempICFEventID
INSERT INTO @TempICFEventID VALUES ('IE-1-12')
SELECT MAX(SplitValue) FROM @TempICFEventID
INSERT INTO @TempICFEventID VALUES ('IE-1-13')
SELECT MAX(SplitValue) FROM @TempICFEventID
INSERT INTO @TempICFEventID VALUES ('IE-1-14')
SELECT MAX(SplitValue) FROM @TempICFEventID
INSERT INTO @TempICFEventID VALUES ('IE-1-15')
SELECT MAX(SplitValue) FROM @TempICFEventID
INSERT INTO @TempICFEventID VALUES ('IE-1-16')
SELECT MAX(SplitValue) FROM @TempICFEventID
INSERT INTO @TempICFEventID VALUES ('IE-1-17')
SELECT MAX(SplitValue) FROM @TempICFEventID
INSERT INTO @TempICFEventID VALUES ('IE-1-18')
SELECT MAX(SplitValue) FROM @TempICFEventID
INSERT INTO @TempICFEventID VALUES ('IE-1-84999')
SELECT MAX(SplitValue) FROM @TempICFEventID
INSERT INTO @TempICFEventID VALUES ('IE-1-85000')
SELECT MAX(SplitValue) FROM @TempICFEventID
Why is this happening
What I have tried:
This is my current logic to generate the ID
DECLARE @Current VARCHAR(900)
DECLARE @Temp1 BIGINT
DECLARE @Temp2 BIGINT
DECLARE @Upper BIGINT
DECLARE @Middle SMALLINT
DECLARE @Lower BIGINT
SET @Lower = -9223372036854775808
SET @Middle = 0
SET @Upper = 9223372036854775807
SELECT @Current = MAX(Ref_ICFEvent_Id) FROM CaseMatrix_ICFEvent
PRINT @Current
DECLARE @TempICFEventID TABLE(ID SMALLINT IDENTITY(1,1),SplitValue VARCHAR(MAX))
IF(@Current IS NOT NULL)
BEGIN
INSERT INTO @TempICFEventID(SplitValue)
SELECT * FROM CaseMatrix_Split(@Current,'-')
SELECT * FROM @TempICFEventID
SELECT @Temp1 = CONVERT(BIGINT,SplitValue) FROM @TempICFEventID WHERE ID = 2
SELECT @Temp2 = CONVERT(BIGINT,SplitValue) FROM @TempICFEventID WHERE ID = 3
IF(@Temp1 IS NOT NULL AND @Temp2 IS NOT NULL)
BEGIN
IF(@Temp1 <> @Upper)
BEGIN
IF(@Temp2 <> @Upper)
BEGIN
SET @Temp2 = @Temp2 + 1
SET @Ref_ICFEvent_Id = 'IE-' + CONVERT(VARCHAR(900),@Temp1) + '-' + CONVERT(VARCHAR(900),@Temp2)
END
ELSE
BEGIN
SET @Temp1 = @Temp1 + 1
IF(@Temp1 <> @Upper)
BEGIN
SET @Temp2 = 1
SET @Ref_ICFEvent_Id = 'IE-' + CONVERT(VARCHAR(900),@Temp1) + '-' + CONVERT(VARCHAR(900),@Temp2)
END
ELSE
BEGIN
SET @Ref_ICFEvent_Id = 'MAXLIMIT'
END
END
END
ELSE
BEGIN
SET @Ref_ICFEvent_Id = 'MAXLIMIT'
END
END
END
ELSE
BEGIN
SET @Ref_ICFEvent_Id = 'IE-1-1'
END