How update a table that contains to a single row but is initially set to NULL and return the next available id

Question

I've got the following code in a stored procedure which works when the row is set to 0:

SET NOCOUNT ON;

BEGIN TRANSACTION

DECLARE @OutputDataID BIGINT

UPDATE [dbo].[MaxDataID]
SET @OutputDataID = [DataID] = [DataID] + 1

COMMIT

RETURN @OutputMetadataID

As it contains only a single row, this works. The DataID gets incremented by 1 and it's updated value gets returned and set in the @OutputDataID variable which is then returned by my stored procedure.

How can I handle this when for the initial value there are no rows in the table? Is the only way to perform a SELECT first and check? I was really hoping to achieve this in a single UPDATE statement.

I've tried COALESCE:

UPDATE [dbo].[MaxDataID]
SET @OutputDataID = [DataID] = COALESCE([DataID],0) + 1

But to no avail. I get the following error when I execute my stored procedure:

'GetNextDataId' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.

And it does not update the database either.

Any ideas?

*"How can I handle this when the initial value is NULL"* do you *actually* mean `NULL` or do you really mean that the table contains *no rows*? — Thom A, Apr 13 '23 at 14:39
Consider using the built-in facilities for this: identity columns and sequences. Writing such code yourself, and ensuring it works correctly in the face of concurrency, is anything but trivial. — Jeroen Mostert, Apr 13 '23 at 14:39
Use the suggested bult-in features for number sequences; Saying that you're trying to return the variable with a null value so why not use `coalesce or isnull` on the return value? — Stu, Apr 13 '23 at 14:52
@ThomA I mean the table has no row. Correct. Sorry about the confusion. — Thierry, Apr 13 '23 at 14:58
That is different from `NULL`, @Thierry . A table with *no rows* contains no values, included `NULL`. So when you `UPDATE` 0 rows you get no value. This is why ` COALESCE([DataID],0) + 1` isn't doing anything; there is no row and no `NULL` value. — Thom A, Apr 13 '23 at 15:01
On a different note, though I know how you could solve this, like the others, switching to an `IDENTITY` or `SEQUENCE` seems a much better solution; why aren't you using either of those? I'm loath to provide a solution which is only open to more problems. — Thom A, Apr 13 '23 at 15:02
@JeroenMostert I appreciate that but unfortunately this does not apply to my scenario. I need this Id to be unique through multiple tables, so I can't have a sequence on each of these tables as this Id value needs to be unique through all these tables, so if 1 is used in table1, it cannot be used in table2, 3, 4, and 5 for example, same if 2 is used in table 2, it cannot be used in table 1,3,4 and 5. Hope this makes sense. This code is very basic but we handle locking through SQL and code and it's worked a charm but wrecking my head having to always set this value to 0 if I don't use a script. — Thierry, Apr 13 '23 at 15:03
Seems like a `SEQUENCE` is *exactly* what you need, and will work for what you want. — Thom A, Apr 13 '23 at 15:04
@Stu I tried that but the problem is when the table has no row (initially) it just fails to update the DataID so there is no point handling the variable as I need the table to have a row inserted with the initial value of 1 I guess and this is where the problem lies. No row, no update but I'm trying to do this without additional statement but I may have to use an UPSERT after all. — Thierry, Apr 13 '23 at 15:05
@ThomA Unfortunately, 'SEQUENCE' is not an option. See my comment to JeroenMosert. — Thierry, Apr 13 '23 at 15:06
@ThomA What you said makes sense and I guess I'll use an UPSERT. Thanks. — Thierry, Apr 13 '23 at 15:08
I think you will find your answer here [https://stackoverflow.com/questions/11010511/how-to-upsert-update-or-insert-in-sql-server-2005](https://stackoverflow.com/questions/11010511/how-to-upsert-update-or-insert-in-sql-server-2005) — Philippe, Apr 13 '23 at 15:08
Your comment to @JeroenMostert only validates that a `SEQUENCE` is what is needed. That is *exactly* how they work: [db<>fiddle](https://dbfiddle.uk/a3JjNpGN) — Thom A, Apr 13 '23 at 15:09
Confused by your assumption that if the table has no rows then `update` will somehow insert a row, how do you expect that to work? — Stu, Apr 13 '23 at 15:13
@ThomA I don't get you. Sorry. Aren't sequence specific to each table which means if I have 5 tables, each will start at 1, then go to 2, and 3, etc... independently from one another but I need this value to be unique at a global level so that each id generated is not shared between these tables, nor do I want to have a table with 10 million records when all I need is a single unique id that is not shared between these table. — Thierry, Apr 13 '23 at 15:15
No, `SEQUENCE`s aren't table specific they are completely separate objects. If you check the db<>fiddle I linked you can see the *same* `SEQUENCE` being used by three *different* tables. Perhaps you are thinking of an `IDENTITY`, which is a *column property*. — Thom A, Apr 13 '23 at 15:17
You can set up sequences to not be specific to a table -- it takes a bit of work but you can have a column that is automatically unique across multiple tables. — Hogan, Apr 13 '23 at 15:19
@Hogan good to know. I'll google it in a sec and thanks for the other update regarding the Merge being slower :) — Thierry, Apr 13 '23 at 15:20
@ThomA will you post this as an answer. This is exactly what I need — Thierry, Apr 13 '23 at 15:22

score 1 · Answer 1 · edited Apr 13 '23 at 15:30

1

you can to use isnull for check return null

return isnull( @OutputDataID ,@defaultIncrement)

and can use if @@ROWCOUNT for check data table for insert data

IF @@ROWCOUNT = 0
   INSERT INTO [MaxDataID] ([DataID]) VALUES (@defaultIncrement)

you can to use @defaultIncrement

alter proc Test
as

SET NOCOUNT ON;

BEGIN TRANSACTION
DECLARE @defaultValue BIGINT=1
DECLARE @defaultIncrement BIGINT=1


DECLARE @OutputDataID BIGINT
UPDATE [dbo].[MaxDataID]
SET @OutputDataID = [DataID] = COALESCE([DataID],0) + @defaultIncrement


IF @@ROWCOUNT = 0
   INSERT INTO [MaxDataID] ([DataID]) VALUES (@defaultValue)


COMMIT

return isnull( @OutputDataID ,@defaultValue)

edited Apr 13 '23 at 15:30

Hogan

69,564
10
76
117

answered Apr 13 '23 at 15:08

abolfazl sadeghi

2,277
2
12
20

I wanted to avoid all of this and hoped to achieve this as a single update but as @Thom A highlighted since my table has no rows, it has nothing to update and I guess there isn't a way to handle this as a single update statement. Looking to MERGE but wondering if what you have is just as good and simpler to read tbh. – Thierry Apr 13 '23 at 15:12
1

@Thierry -- merge is a slower process than this -- so it won't be faster. – Hogan Apr 13 '23 at 15:13
`defaultValue` is an interesting name for an increment. maybe `@defaultIncrement` is better? Then also include another variable for the default value. I'm assuming they are not always the same thing. – Hogan Apr 13 '23 at 15:14
I Researched about your request but i didn't a way to handle this as a single update statement. if you will a way please tell me @Thierry – abolfazl sadeghi Apr 13 '23 at 15:22
@Heminsadeghi See Thom A's comment about SEQUENCE. This is exactly what I need to handle my scenario and will make it a lot easier to handle and let SQL Server do the work. – Thierry Apr 13 '23 at 15:23

Thom A · Accepted Answer · 2023-04-13T15:35:31.763

What you have here appears to be an XY Problem. As has been mentioned in the comments multiple times, SQL Server has facilities to handle the creation of incrementing values by the use of IDENTITY and SEQUENCE. An IDENTITY is a column property, whereas a SEQUENCE is a database object and so can be referenced by multiple tables.

Normally, when using a SEQUENCE you will define the use of it in a DEFAULT CONSTRAINT, then in a similar way to IDENTITY you can omit the column from the INSERT clause and SQL Server will handle the values automatically.

As we don't have a shema for your tables here, I use some simple table to demonstrate the use of both IDENTITY and SEQUENCE, though it seems it's the latter you want.

CREATE SEQUENCE dbo.MySequence
    START WITH 1 INCREMENT BY 1;
GO

CREATE TABLE dbo.MyTable (ID int IDENTITY(1,1),
                          SequenceVal int NOT NULL CONSTRAINT DF_MyTableSequenceVal DEFAULT NEXT VALUE FOR dbo.MySequence,
                          SomeDate date NULL,
                          TableName AS N'MyTable');

CREATE TABLE dbo.YourTable (ID int IDENTITY(1,1),
                            SequenceVal int NOT NULL CONSTRAINT DF_YourTableSequenceVal DEFAULT NEXT VALUE FOR dbo.MySequence,
                            SomeDate date NULL,
                            TableName AS N'YourTable');

CREATE TABLE dbo.AnotherTable (ID int IDENTITY(1,1),
                               SequenceVal int NOT NULL CONSTRAINT DF_AnotherTableSequenceVal DEFAULT NEXT VALUE FOR dbo.MySequence,
                               SomeDate date NULL,
                               TableName AS N'AnotherTable');
GO

INSERT INTO dbo.MyTable
DEFAULT VALUES;
INSERT INTO dbo.MyTable (SomeDate)
VALUES(GETDATE())

INSERT INTO dbo.YourTable
DEFAULT VALUES;

INSERT INTO dbo.MyTable
DEFAULT VALUES;

INSERT INTO dbo.AnotherTable (SomeDate)
VALUES(DATEADD(DAY, -1,GETDATE()))

INSERT INTO dbo.MyTable (SomeDate)
VALUES(DATEADD(DAY, -2,GETDATE()))
GO

SELECT *
FROM dbo.MyTable
UNION ALL
SELECT *
FROM dbo.YourTable
UNION ALL
SELECT *
FROM dbo.AnotherTable
ORDER BY SequenceVal ASC;

GO

DROP TABLE dbo.MyTable;
DROP TABLE dbo.YourTable;
DROP TABLE dbo.AnotherTable;
DROP SEQUENCE dbo.MySequence;

This results in the following dataset:

ID	SequenceVal	SomeDate	TableName
1	1	NULL	MyTable
2	2	2023-04-13	MyTable
1	3	NULL	YourTable
3	4	NULL	MyTable
1	5	2023-04-12	AnotherTable
4	6	2023-04-11	MyTable

Notice that the value of the identity repeats, but the SEQUENCE is unique.

Great answer! and thanks for clarifying the differences between the 2. Learn something new today, so thanks again :) — Thierry, Apr 13 '23 at 15:32

How update a table that contains to a single row but is initially set to NULL and return the next available id

2 Answers2