Incrementer dynamiquement une sequence

Version imprimable

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:

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:

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:

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:

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:

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?

26/03/2014, 10h26
SQLpro

Utilisez sp_executesql pour lier vos variables dynamiques et statiques.

A +
26/03/2014, 20h22
SOPSOU
Thanks SQLPro,
Effectivement sp_executesql permet de lancer l'éxécution en faisant:
Code:

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:

SET @Sequence = EXECUTE sp_executesql @NomSequence

Ou est ce que je me prends mal?
27/03/2014, 09h59
aieeeuuuuu

Bonjour,

Citation:

Envoyé par SOPSOU

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 ?
27/03/2014, 11h09
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.
27/03/2014, 11h33
aieeeuuuuu

Citation:

Envoyé par SOPSOU

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 :

Citation:

Envoyé par SOPSOU

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 ?
27/03/2014, 17h28
SOPSOU

Thanks,

Cela est gênant parce que ce sont des numéros d'ordre qui sont attribués(pour rappel, ces numéros sont formatés SET @NumeroBranchement = @Racine + RIGHT('0000000000' + @Sequence, 10) ). Le cahier de charge me demande cela.
Pour l'inquiétude 2, on ne peut pas supprimé mais plutôt désactivé ce branchement et si cela arrivait, on continue la suite. Et c'est pour cela, la mise à jour de NumeroBranchement est fait sur AFTER INSERT.
Pour résumé, considères un chéquier qui est ordonné donc si on fait une erreur de saisie sur un chéquier, la numérotation suivante reste inchangée. Mais pour mon cas, J'ai différent type de chéquier et pour chaque type de chéquier, j'ai un ordre chronologique.
27/03/2014, 18h01
aieeeuuuuu
Pour ma part, je m'inspirerais de ceci, et stockerais les numéros dans la table TypeBranchement, plutôt que de créer des séquences...

Mais concernant votre difficulté pour récupérer le résultat de votre sp_executesql, il faut définir une variable OPUTPUT, comme dans l'exemple suivant :
(pas testé)
Code:

1 2 3 4 5 6 7 8 DECLARE @SQL NVARCHAR(50) DECLARE @var INT SET @SQL = 'SELECT @var = NEXT VALUE FOR ' + @NomSequence EXEC sp_executesql @SQL, N'@var INT OUTPUT', @var=@var OUTPUT SELECT @var

thanks,
Cool pour ta réponse
J'ai fait cela et ça marche aussi:

Code:

1
2
3
4
5
6
7
DECLARE @SQL NVARCHAR(50)
DECLARE @var SQL_Variant
SET @SQL = 'SELECT @var = NEXT VALUE FOR ' + @NomSequence
EXEC sp_executesql @SQL
SET @var = (SELECT CURRENT_VALUE FROM sys.sequences where name = @NomSequence)
SELECT @var

Thanks à tous.

28/03/2014, 11h19
aieeeuuuuu

Bonjour,

Accéder de la sortes aux table système n'est généralement pas une bonne idée. il vous faudra accorder les privilèges aux utilisateurs pour ces tables.

Par ailleurs, si deux insertions ont lieu simultanément, les résultats pourraient être erronés...