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

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?

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)

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:

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