SQL Stored Procedure to Generate Sequential Number

While much of the work I do is directly with Microsoft Dynamics GP, I also do work for clients which isn’t directly related. I’ve created code to generate numbers a few times in the past and figured I might as well post the base code I use for this to make it easier to find in future.

I’ve created it in such a way that several unique numbers can be stored and incremented.

The first part of the code creates a table to hold the number type and next number:

-- drop table if it exists
IF OBJECT_ID (N'ut_AZRCRV_NextNumber', N'U') IS NOT NULL
	DROP TABLE ut_AZRCRV_NextNumber
GO
-- create table
CREATE TABLE ut_AZRCRV_NextNumber(
	NMBRTYPE VARCHAR(50)
	,NEXTNMBR INT
)
GO

Next, I create a stored procedure which will increment and return the next number:

-- drop stored proc if it exists
IF OBJECT_ID (N'usp_AZRCRV_GetNextNumber', N'P') IS NOT NULL
    DROP PROCEDURE usp_AZRCRV_GetNextNumber
GO
-- create stored proc
CREATE PROCEDURE [dbo].[usp_AZRCRV_GetNextNumber]
	(
	@NMBRTYPE VARCHAR(50)
	,@NEXTNMBR INT OUTPUT
	)
AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
	SET NOCOUNT ON

	BEGIN TRAN
		-- if this is the first value generated for this table, start with one
		IF NOT EXISTS (SELECT * FROM ut_AZRCRV_NextNumber WHERE NMBRTYPE = @NMBRTYPE)
			INSERT INTO ut_AZRCRV_NextNumber
				(NMBRTYPE,NEXTNMBR)
			VALUES
				(@NMBRTYPE,1)
		
		-- select next number from table into variable
		SELECT @NEXTNMBR = NEXTNMBR FROM ut_AZRCRV_NextNumber WHERE NMBRTYPE = @NMBRTYPE
			
		-- increment number by 1
		UPDATE
			ut_AZRCRV_NextNumber
		SET
			NEXTNMBR = NEXTNMBR + 1
		WHERE
			NMBRTYPE = @NMBRTYPE
	COMMIT TRAN
	
	-- return variable containing next number
	RETURN @NEXTNMBR
GO

Then, I grant execute permissions to the relevant database role:

-- grant execute permission on stored proc to ur_AZRCRV_InvoiceUser
GRANT EXECUTE ON usp_AZRCRV_GetNextNumber TO ur_AZRCRV_InvoiceUser
GO

And finally, I have the SQL code which will generate the next number:

-- code to get next number
DECLARE @NMBRTYPE VARCHAR(50) = 'Sales Invoice'
DECLARE @NEXTNMBR INT

EXEC [usp_AZRCRV_GetNextNumber] @NMBRTYPE, @NEXTNMBR OUTPUT

SELECT @NEXTNMBR
GO