SQL Stored Procedure to Generate Sequential Number

Microsoft SQL ServerWhile 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