Dans certains développements on exige une numérotation de certaines données dépendantes d’une information telle que le client ou l’année. Voyonc comment réaliser une telle chose avec MS SQL Server.
Principe :
Le principe est de réaliser une table de compteurs et d’y associer une procédure afin de récupérer une valeur de compteur.
En effet, comme nous l’avons dit dans cet article, l’utilisation d’un MAX(…) + 1 est la pire abomination en terme de performances, en sus de conduire immanquablement un jour ou l’autre à des télescopage (2 clefs identiques).
Cette table doit s’appuyer sur une table des tables, afin de n’utiliser qu’une clef numérique comme identifiant de la table à comptabiliser…
Voici un exemple d’une telle table de compteur :
CREATE TABLE dbo.T_TABLE_TBL
(TBL_ID INT NOT NULL IDENTITY PRIMARY KEY,
TBL_NAME sysname UNIQUE);
GO
CREATE TABLE dbo.T_COMPTEUR_CPT
(CPT_ID INT NOT NULL IDENTITY PRIMARY KEY,
TBL_ID INT NOT NULL FOREIGN KEY REFERENCES T_TABLE_TBL (TBL_ID),
CPT_FKID INT NOT NULL, --> valeur du compteur relatif (n° de client, n° d'exercice...)
CPT_VALEUR INT NOT NULL DEFAULT 0,
CONSTRAINT UK_CPT_TBL_RLT UNIQUE (TBL_ID, CPT_FKID));
GO
La procédure pour récupérer une valeur de compteur est la suivante :
CREATE PROCEDURE dbo.P_GET_NEXT_COMPTEUR
@TABLE_NAME NVARCHAR(128),
@CPT_FKID INT,
@NEW_ID INT OUTPUT
AS
SET NOCOUNT ON;
--> ce comptage existe t-il ?
IF NOT EXISTS(SELECT *
FROM dbo.T_COMPTEUR_CPT AS C
INNER JOIN dbo.T_TABLE_TBL AS T
ON C.TBL_ID = T.TBL_ID
WHERE TBL_NAME = @TABLE_NAME
AND C.CPT_FKID = @CPT_FKID)
BEGIN
-- non, alors primo insertion !
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
INSERT INTO dbo.T_TABLE_TBL (TBL_NAME)
SELECT @TABLE_NAME
WHERE NOT EXISTS(SELECT *
FROM dbo.T_TABLE_TBL
WHERE TBL_NAME = @TABLE_NAME);
IF @@ERROR <> 0 GOTO LBL_ERROR;
INSERT INTO dbo.T_COMPTEUR_CPT (TBL_ID, CPT_FKID)
SELECT TBL_ID, @CPT_FKID
FROM dbo.T_TABLE_TBL
WHERE TBL_NAME = @TABLE_NAME;
IF @@ERROR <> 0 GOTO LBL_ERROR;
END;
-- récupéartion de la valeur de la clef en même temps que l'incrémentation se fait
UPDATE C
SET CPT_VALEUR = CPT_VALEUR + 1,
@NEW_ID = CPT_VALEUR + 1
FROM dbo.T_COMPTEUR_CPT AS C
INNER JOIN dbo.T_TABLE_TBL AS T
ON C.TBL_ID = T.TBL_ID
WHERE TBL_NAME = @TABLE_NAME
AND C.CPT_FKID = @CPT_FKID;
IF @@ERROR <> 0 GOTO LBL_ERROR;
-- gestion transaction
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
RETURN
-- gestion d'erreur
LBL_ERROR:
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
RAISERROR('Impossible de calculer une nouvelle clef relative (procedure dbo.P_GET_NEXT_COMPTEUR )', 16, 1);
GO
Utilisation dans un code :
EXEC dbo.P_GET_NEXT_COMPTEUR 'titi', 108, @NEW_ID OUTPUT;
SELECT @NEW_ID AS NEW_ID;
Mais comment faire lorqu’il y a plusieurs clef à calculer simultanément comme c’est le cas souvent des imports de données ?
On doit alors passer par une fonction table et modifier légèrement la procédure précédente…
La fonction table, qui calcule simultanément tous les ID :
CREATE FUNCTION F_NUM (@DEBUT INT, @FIN INT)
RETURNS @T TABLE (NUM INT)
AS
BEGIN
IF @DEBUT > @FIN RETURN;
IF @DEBUT IS NULL RETURN;
IF @FIN IS NULL SET @FIN = @DEBUT;
WHILE @DEBUT <= @FIN
BEGIN
INSERT INTO @T VALUES (@DEBUT);
SET @DEBUT = @DEBUT + 1;
END
RETURN;
END;
GO
La procédure modifiée :
CREATE PROCEDURE dbo.P_GET_NEXT_N_COMPTEUR
@TABLE_NAME NVARCHAR(128),
@CPT_FKID INT,
@NB_ID INT, -- nombre de clef demandées
@NEW_ID INT OUTPUT
AS
SET NOCOUNT ON;
--> ce comptage existe t-il ?
IF NOT EXISTS(SELECT *
FROM dbo.T_COMPTEUR_CPT AS C
INNER JOIN dbo.T_TABLE_TBL AS T
ON C.TBL_ID = T.TBL_ID
WHERE TBL_NAME = @TABLE_NAME
AND C.CPT_FKID = @CPT_FKID)
BEGIN
-- non, alors primo insertion !
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
INSERT INTO dbo.T_TABLE_TBL (TBL_NAME)
SELECT @TABLE_NAME
WHERE NOT EXISTS(SELECT *
FROM dbo.T_TABLE_TBL
WHERE TBL_NAME = @TABLE_NAME);
IF @@ERROR <> 0 GOTO LBL_ERROR;
INSERT INTO dbo.T_COMPTEUR_CPT (TBL_ID, CPT_FKID)
SELECT TBL_ID, @CPT_FKID
FROM dbo.T_TABLE_TBL
WHERE TBL_NAME = @TABLE_NAME;
IF @@ERROR <> 0 GOTO LBL_ERROR;
END;
-- récupération de la valeur de la clef en même temps que l'incrémentation se fait
UPDATE C
SET CPT_VALEUR = CPT_VALEUR + @NB_ID,
@NEW_ID = CPT_VALEUR + @NB_ID
FROM dbo.T_COMPTEUR_CPT AS C
INNER JOIN dbo.T_TABLE_TBL AS T
ON C.TBL_ID = T.TBL_ID
WHERE TBL_NAME = @TABLE_NAME
AND C.CPT_FKID = @CPT_FKID;
IF @@ERROR <> 0 GOTO LBL_ERROR;
-- gestion transaction
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
RETURN;
-- gestion d'erreur
LBL_ERROR:
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
RAISERROR('Impossible de calculer une nouvelle clef relative (procedure dbo.P_GET_NEXT_COMPTEUR )', 16, 1);
GO
Un exemple d’appel :
DECLARE @NEW_ID INT, @NB_ID INT;
SET @NB_ID = 3;
EXEC dbo.P_GET_NEXT_N_COMPTEUR 'titi', 108, @NB_ID, @NEW_ID OUTPUT;
SELECT *
FROM dbo.F_NUM(@NEW_ID, @NEW_ID + @NB_ID - 1)
--------
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/
Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server
www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *
La fonction SELECT * FROM dbo.F_NUM(@NEW_ID, @NEW_ID + @NB_ID Р1) ne marche pas car @NEW_ID correspond au dernier compteur rajout̩, et non au premier (@NEW_ID = CPT_VALEUR + @NB_ID )
il faudrait plutôt utiliser
SELECT * FROM dbo.F_NUM(@NEW_ID – @NB_ID +1 , @NEW_ID)
A+