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
Can ISC Software Solutions help?
ISC Software Solutions are UK and Ireland based experts on Microsoft Dynamics GP. If you'd like to speak to someone about support, consultancy, upgrades, implementation, development, GP Elementz add-ons or portals, or anything else Dynamics GP related, you can use the form below.