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 an IT Professional (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
What should we write about next?
If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.