Skip to the content

Hoe maak ik mijn eigen autonummering in SQL Server

Wanneer je gebruik maakt van de Identity optie in de tabellen van SQL Server dan is het niet meer mogelijk om de waarde achteraf aan te passen. Echter, er zijn in sommige gevallen redenen om dit wel te willen. Een andere reden om de identity niet te gebruiken is het gebruiken van eigen nummerreeksen zoals factuurnummers die je ieder jaar aan wilt kunnen passen.

Om deze reden heb ik een stored procedure voor sql server geschreven die deze handelingen voor je uitvoert.

De eerste stap is het maken van een tabel, in sql server, waar we de autonummer records in opslaan.

De primaire sleutel is de tabelnaam, veldnaam en de range index. Doordat we bijvoorbeeld per jaar ranges aan kunnen maken, door de begin en de einddatum mee te geven in de procedure, is de range index het volgnummer van de range.

/****** Object:  Table [dbo].[Autonum]    Script Date: 07/24/2015 13:50:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Autonum](
	[AutoNumTable] [varchar](30) NOT NULL,
	[AutoNumField] [varchar](30) NOT NULL,
	[AutoNumRangeId] [int] NOT NULL,
	[AutoNumDescription] [varchar](50) NULL,
	[AutoNumStartValue] [int] NULL,
	[AutoNumMaxValue] [int] NULL,
	[AutoNumActualValue] [int] NULL,
	[AutoNumStep] [int] NULL,
	[AutoNumStartDate] [datetime] NULL,
	[AutoNumEndDate] [datetime] NULL,
 CONSTRAINT [PK_Autonum] PRIMARY KEY CLUSTERED 
(
	[AutoNumTable] ASC,
	[AutoNumField] ASC,
	[AutoNumRangeId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

De stored procedure


Nu kun je de hieronder weergegeven stored procedure in SQL Server uitvoeren.

/****** Object:  StoredProcedure [dbo].[usp_Add_NewIdInt]    Script Date: 07/24/2015 13:53:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[usp_Add_NewIdInt]
	@TABLEID		VARCHAR(30),
	@FIELDID		VARCHAR(30),
	@CURRENTDATE	DATETIME,
	@STARTVAL		INT = NULL,
	@ENDVAL			INT = NULL,
	@STATDATE		DATETIME = NULL,
	@ENDDATE		DATETIME = NULL,
	@RET			INT OUTPUT /*Return*/
AS
	SET @RET = 0
	
	/*DECLARATIE*/
	DECLARE @RANGEID INT --ROW IDENTIFIER
	DECLARE @GO BIT --RUN NEXT PART OF PROC
	DECLARE @MINVALUE INT 
	DECLARE @MAXVALUE INT

	/*STANDAARD WAARDEN*/

	SET @GO = 'True'

	/*ZOEK DE RANGE AAN DE HAND VAN DE HUIDIGE DATUM*/
	SELECT @RANGEID = AutoNumRangeId
	FROM dbo.Autonum
	WHERE AutoNumTable = @TABLEID 
		AND AutoNumField = @FIELDID
		AND (AutoNumStartDate < @CURRENTDATE AND AutoNumEndDate > @CURRENTDATE)
	

	IF @RANGEID IS NULL
		BEGIN
			SELECT @RANGEID = MAX(AutoNumRangeId) 
			FROM dbo.Autonum 
			WHERE AutoNumTable = @TABLEID 
			AND AutoNumField = @FIELDID
	
			IF @RANGEID IS NULL
				BEGIN
					SET @RANGEID = 1
					SET @MINVALUE = CASE WHEN COALESCE(@STARTVAL,0) > 0 THEN @STARTVAL ELSE 0 END
					SET @MAXVALUE = CASE WHEN COALESCE(@ENDVAL,0) > 0 THEN @ENDVAL ELSE 99999 END
				END
			ELSE
				BEGIN
					SET @RANGEID = @RANGEID + 1
					SET @MINVALUE = CASE WHEN COALESCE(@STARTVAL,0) > 0 THEN @STARTVAL ELSE 0 END
					SET @MAXVALUE = CASE WHEN COALESCE(@ENDVAL,0) > 0 THEN @ENDVAL ELSE 99999 END
				END
				
			INSERT INTO dbo.Autonum(
				AutoNumTable,
				AutoNumField,
				AutoNumRangeId,
				AutoNumDescription,
				AutoNumStartValue,
				AutoNumMaxValue,
				AutoNumActualValue,
				AutoNumStep,
				AutoNumStartDate,
				AutoNumEndDate
				)
			VALUES(
				@TABLEID,
				@FIELDID,
				@RANGEID,
				'<Generated by sproc>',
				@MINVALUE,
				@MAXVALUE,
				@MINVALUE,
				1,
				COALESCE(@STATDATE,GETDATE()),
				COALESCE(@ENDDATE,DATEADD(DAY,365,GETDATE()))
				)
			
			SET @RET = @MINVALUE	
		END
	ELSE
		BEGIN
			
			DECLARE @NEWID INT
			SELECT @NEWID =  ISNULL((AutoNumActualValue+AutoNumStep), 0)
			FROM dbo.Autonum 
			WHERE AutoNumTable = @TABLEID 
			AND AutoNumField = @FIELDID
			AND AutoNumRangeId = @RANGEID
			
			UPDATE dbo.Autonum 
			SET AutoNumActualValue = @NEWID 
			FROM dbo.Autonum 
			WHERE AutoNumTable = @TABLEID 
			AND AutoNumField = @FIELDID
			AND AutoNumRangeId = @RANGEID
	
			SET @RET = @NEWID
		END
		

	RETURN @RET


GO

De opgeslagen procedure uitgelegd

De parameters @TABLEID, @FIELDID en @CURRENTDATE zijn verplichte parameters. Respectievelijk geef je hier de tabelnaam, veldnaam en de datum mee. Op deze manier kan je bijvoorbeeld een factuur aanmaken in een ander boekjaar dan het huidige.

De overige parameters zijn optioneel en worden uitsluitend gebruikt om nieuwe regels toe te voegen aan de autonummering range.

De autonummering aan het werk

Om een nieuwe range toe te voegen aan de autonummering gebruik je onderstaande opdracht. Hierbij geen je naast de tabelnaam, veldnaam en datum ook de startwaarde, eindwaarde, startdatum van de reeks en de einddatum van de reeks mee.

Bijvoorbeeld we willen een nieuwe reeks factuurnummer maken voor de tabel tblFacturen en het veld Factuurnummer voor het boekjaar 2015. In onderstaande voorbeeld geven we als nummerrange 2015001 t/m 2015999 mee. Buiten deze range mag geen nieuw id worden teruggegeven. Als datum range geven we 1 januari 2015 tot en met 31 december 2015 mee. Buiten deze datumrange mag geen waarde worden teruggegeven.

DECLARE @NEWID INT

EXEC dbo.usp_Add_NewIdInt N'tblFacturen', N'Factuurnummer',N'2015-10-20', '2015001', '2015999', '2015-01-01', '2015-12-31', @NEWID OUTPUT

Reinder.eu | nieuwe reeks autonummering sql server

Opvragen nieuw volgnummer

Het opvragen van een nieuw factuurnummer in de reeks kun je doen volgens onderstaande voorbeeld waarbij de optionele parameters leeg mogen zijn.

DECLARE @NEWID INT

EXEC dbo.usp_Add_NewIdInt N'tblFacturen', N'Factuurnummer',N'2015-10-20', NULL, NULL, NULL, NULL, @NEWID OUTPUT

SELECT @NEWID