“reserves a row” in a SQL Server table.

Author: Alexander Christov

"reserves a row" in a SQL Server table. The table must have an IDENTITY PK. Identity seed and increment may vary (not necessarily 1, 1!).

USE [TestDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[ReserverRow](@TableName nvarchar(80))
AS
BEGIN
        BEGIN TRY
                DECLARE @NewIdentity int
                SELECT @NewIdentity = Ident_Current(@TableName) + Ident_Incr(@TableName)

                DBCC CHECKIDENT(@TableName, RESEED, @NewIdentity)

                -- Return the result of the function
                RETURN @NewIdentity
        END TRY
        BEGIN CATCH
                SELECT ERROR_MESSAGE() as ErrorMessage;
                RETURN -ERROR_NUMBER()
        END CATCH;
END