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