Discussion :

[SOPSOU]

Bonjour,
Je crée dynamiquement des séquences dans ma BD dans SQL Server 2012 et j'aimerai pouvoir appeler ma séquence dynamiquement pour récupérer le NEXT VALUE.

Table TypeBranchement

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
CREATE TABLE [dbo].[TypeBranchement](
	[IdTypeBranchement] [int] IDENTITY(1,1) NOT NULL,
	[LibelleTypeBranchement] [varchar](250) NOT NULL,
 CONSTRAINT [PK_TypeBranchement] PRIMARY KEY CLUSTERED 
(
	[IdTypeBranchement] 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

Création dynamique d'un trigger créant dynamiquement des sequences après un Insert sur la table TypeBranchement

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
CREATE TRIGGER [dbo].[CreationSequence] 
   ON  [dbo].[TypeBranchement] 
   AFTER INSERT
AS 
BEGIN
 
	SET NOCOUNT ON;
	DECLARE  @TypeBranchement varchar(200), @VarSequence Varchar(200)
	SET @TypeBranchement = (SELECT LibelleTypeBranchement FROM inserted I )
        SET @TypeBranchement = UPPER(REPLACE(@TypeBranchement,' ', ''))	
	SET @VarSequence = 'CREATE SEQUENCE [dbo].[' + @TypeBranchement + ']  START WITH 0  INCREMENT BY 1  NO CACHE'
	EXECUTE (@varSequence)
END

NB: La colonne LibelleTypeBranchement est UNIQUE dans ma BD

Table Branchement

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
CREATE TABLE [dbo].[Branchement](
	[IdBranchement] [Int] IDENTITY(1,1) NOT NULL,
	[NumeroBranchement] [varchar](20) NULL,
	[NomAbonne] [varchar](100) NULL,
	[IdTypeBranchement] [int] NOT NULL,
 CONSTRAINT [PK_Branchement] PRIMARY KEY CLUSTERED 
(
	[IdBranchement] 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
ALTER TABLE [dbo].[Branchement]  WITH CHECK ADD  CONSTRAINT [FK_Branchement_TypeBranchement] FOREIGN KEY([IdTypeBranchement])
REFERENCES [dbo].[TypeBranchement] ([IdTypeBranchement])
GO
ALTER TABLE [dbo].[Branchement] CHECK CONSTRAINT [FK_Branchement_TypeBranchement]
GO

Insertion sur la table TypeBranchement

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
INSERT INTO [dbo].[TypeBranchement]  (LibelleTypeBranchement) VALUES ('Test1')
INSERT INTO [dbo].[TypeBranchement]  (LibelleTypeBranchement) VALUES ('Test2')
INSERT INTO [dbo].[TypeBranchement]  (LibelleTypeBranchement) VALUES ('Test3')

Après exécution de ces insertions dans la table [dbo].[TypeBranchement], J'aurai 03 sequences qui seront crées
Nom Séquence 1:[dbo].[Test1]
Nom Séquence 2:[dbo].[Test1]
Nom Séquence 3:[dbo].[Test1]

Et voila ce que je veux faire

Lors d'une insertion dans [dbo].[Branchement], la valeur NumeroBranchement doit correspondre à la valeur d'une séquence en fonction de la clé se trouvant dans
[dbo].[Branchement] ([IdTypeBranchement])

Essai de trigger sur la table [dbo].[Branchement]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE TRIGGER [cefore].[NumeroBranchement] 
   ON  [dbo].[Branchement]
      AFTER INSERT
AS 
BEGIN
	SET NOCOUNT ON;
	DECLARE  @Racine varchar(20), @Sequence bigint, @NomSequence Varchar(200), @NumBranchement Varchar(100), @LibelleTypeNumBranchement varchar(200)
	SET @Racine= N'PPI' 
	SET @LibelleTypeBranchement = (SELECT LibelleTypeBranchement FROM [dbo].[TypeBranchement] WHERE [IdTypeBranchement] = (Select IdTypeBranchement From inserted))
	SET @LibelleTypeBranchement = UPPER(REPLACE(@LibelleTypeBranchement,' ', ''))
	 
	SET @NomSequence = @LibelleTypeBranchement
	SET @NomSequence = '[dbo].[' + @NomSequence + ']'

	SET @NomSequence = 'NEXT VALUE FOR ' + @NomSequence		
	SET @Sequence = (EXECUTE @NomSequence)
	SET @Sequence = CAST(@Sequence AS varchar(100))
	SET @NumBranchement = @Racine + RIGHT('0000000000' + @Sequence, 10)
 	UPDATE [dbo].[Branchement] SET [NumeroBranchement] = @NumBranchement WHERE [IdBranchement] = (SELECT IdBranchement From inserted)

END

Voila, SQL Server me permet pas de faire:
SET @NomSequence = 'NEXT VALUE FOR ' + @NomSequence
SET @Sequence = (EXECUTE @NomSequence)
Comment procéder pour appéler dynamiquement une sequence en ne connaissant son nom que par des variables comme décrit ci-dessus?

[SQLpro]

Utilisez sp_executesql pour lier vos variables dynamiques et statiques.

A +

[SOPSOU]

Thanks SQLPro,
Effectivement sp_executesql permet de lancer l'éxécution en faisant:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SET @NomSequence = 'NEXT VALUE FOR ' + @NomSequence		
EXECUTE sp_executesql @NomSequence

Mais je peux pas faire l'affectation de cette valeur comme voulu du genre:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SET @Sequence = EXECUTE sp_executesql @NomSequence

Ou est ce que je me prends mal?

[aieeeuuuuu]

Bonjour,

Ou est ce que je me prends mal?

Je pense que oui...

1/ en premier lieu, votre trigger sur TypeBranchement ne fonctionnera pas si vous insérez plusieurs lignes en même temps.
2/ ensuite, quel est l’intérêt de ces numéros ? A quoi doivent-ils servir et pourquoi ne pas simplement créer une unique séquence pour tous les types ?

[SOPSOU]

Thanks,
L'inquiétude 1 n'arrivera jamais. Et au finish, ma table TypeBranchement ne comportera pas plus 10 lignes. Le seul problème, c'est que son Libelle est inconnu actuellement.
L'inquiétude 2: En fait pour chaque typeBranchement, je dois avoir avoir des numeros d'ordre chronologique car utilisé et attribué à un abonné.

Exemple:
Pour le TypeBranchement 1, j'aurai NumeroBranchement qui pendra les valeurs 1,2,3,4,5,6,7 et ainsi de suite sans interruption.
Pour le TypeBranchement 2, j'aurai NumeroBranchement qui pendra les valeurs 1,2,3,4,5,6,7 et ainsi de suite sans interruption.
Pour le TypeBranchement 3, j'aurai NumeroBranchement qui pendra les valeurs 1,2,3,4,5,6,7 et ainsi de suite sans interruption.
...

Et cela est essentiel pour moi, j'ai pour cela je cree dynamiquement, des sequences pour chaque typeBranchement et lors de l'insertion dans ma table Branchement, je sais quelle sequence a utilisée suivant le IdTypeBranchement utilisé pour l'enregistrement dans la table Branchement.

[aieeeuuuuu]

L'inquiétude 2: En fait pour chaque typeBranchement, je dois avoir avoir des numeros d'ordre chronologique car utilisé et attribué à un abonné.

Oui, mais utilisé pour quoi ??? en quoi cela sera-t-il genant si les numéros ne se suivent pas.

Car :

Pour le TypeBranchement 1, j'aurai NumeroBranchement qui pendra les valeurs 1,2,3,4,5,6,7 et ainsi de suite sans interruption.

Que se passera-t-il si vous supprimez le 3 ? décalerez vous 4,5,6,7 ?