Insert() Select & Fonctions scalaires

Version imprimable

20/11/2015, 08h42
smilee44

Insert() Select & Fonctions scalaires

Bonjour,

Je suis nouveau sur le forum pardonnez-moi si je fais des erreurs de jeunesse...

Voila je ne sais pas comment résoudre mon problème.

Je fais un INSERT dans une table Facture a partir d'un SELECT d'une table Commande. Afin d'initialiser la création de la facture. Dans la facture j'ai un champ numeroFacture dans lequel on créé un numéro de type 'FF' + mois sur 2 chiffres + numéro auto sur 5 chiffres. J'ai créé une fonction scalaire pour retourner ce numéro.

Je veux donc créé plusieurs factures d'un coup et la, ma fonction retourne toujours le même numéro de facture pour toutes les lignes.

Je ne vois pas comment solutionner mon problème pour qu'a chaque ligne le numéro soit incrémenter.

Des idées ?

Voici un aperçu du code

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
INSERT INTO dbo.FAAE
           (NoFacture
           ,CodeFournisseur
           ,DateMajFact
           ,DateFacturation
           ,NumeroFacture
           ,DateEffetPiece
           )
     SELECT DISTINCT
            F.NoFacture				--NoFacture
           ,A.CodeFournisseur			--CodeFournisseur
           ,convert(varchar,getdate(),112)	--DateMajFact
           ,F.DateFacture				--DateFacturation
           ,dbo.it_FAA_generation_nouveau_numero_Facture()	-- NumeroFacture
           ,F.DateFacture				--DateEffetPiece
FROM FAAE_TEMP_AJOUT F
	INNER JOIN ACHE A
	ON F.NoConfirmation = A.NoAccuseReception
WHERE F.Ident = @Ident
AND F.AIntegre = 1

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE FUNCTION [dbo].[it_FAA_generation_nouveau_numero_Facture]()
RETURNS varchar(30)
AS
 
BEGIN
 
	DECLARE
		@NouveauNumeroFacture varchar(30)	
 
	SELECT @NouveauNumeroFacture = (SELECT  PrefixeAlphaFacta Prefixe FROM dbo.PARAMG WHERE NumeroFiche=1 ) +RIGHT('00000'+CAST(RIGHT(Max(numerofacture),5)+1 AS VARCHAR),5) 
	FROM FAAE 
	WHERE LEFT(numerofacture,6)= 
	(
		SELECT  PrefixeAlphaFacta Prefixe FROM dbo.PARAMG WHERE NumeroFiche=1 
	)
 
	RETURN @NouveauNumeroFacture
END

Bonjour,

Votre fonction ne prend pas de paramètres en entrée.
Comme par ailleurs vous retournez une valeur qui celle de la première ligne retournée par le SELECT PrefixeAlphaFacta Prefix ...., vous obtenez toujours la même valeur.

Par ailleurs, les fonctions scalaires (et de table à plusieurs instructions) sont appelées une fois pour chaque ligne de jeu de données.
Elles ne sont donc pas exécutées de façon ensembliste (comme le sont les fonctions de table en ligne), et sont donc contre-performantes.

Une solution que je vois est la suivante :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
DECLARE @PrefixeAlphaFacta varchar(50)
	, @numeroFacture int
 
BEGIN TRY
	BEGIN TRANSACTION
		-- Ceci fonctionne correctement si les valeurs
		-- de la colonne NumeroFiche sont uniques
		SELECT	@PrefixeAlphaFacta = PrefixeAlphaFacta
		FROM	Prefixe
		FROM	dbo.PARAMG
		WHERE	NumeroFiche=1
 
		SELECT	@numeroFacture = MAX(numerofacture)
		FROM	dbo.FAAE
		WHERE	LEFT(numerofacture, 6) = @PrefixeAlphaFacta
 
		SELECT		DISTINCT F.NoFacture --NoFacture
				, A.CodeFournisseur --CodeFournisseur
				, CONVERT(VARCHAR, GETDATE(), 112) --DateMajFact
				, F.DateFacture	--DateFacturation
				, @PrefixeAlphaFacta + RIGHT('00000' + CAST(RIGHT(MAX(@numeroFacture), 5) + 1 AS varchar),5) 
				, F.DateFacture --DateEffetPiece
		FROM		dbo.FAAE_TEMP_AJOUT AS F
		INNER JOIN	dbo.ACHE AS A
					ON F.NoConfirmation = A.NoAccuseReception
		WHERE		F.Ident = @Ident
		AND		F.AIntegre = 1
	COMMIT TRANSACTION
END TRY
BEGIN CATCH
	DECLARE @err_msg nvarchar(4000) = 'Line ' + CAST(ERROR_LINE() AS varchar(10)) + ' - ' + ERROR_MESSAGE()
		, @err_svt int = ERROR_SEVERITY()
		, @err_stt int = ERROR_STATE()
 
	IF XACT_STATE() <> 0
	BEGIN
		ROLLBACK TRANSACTION;
	END
 
	RAISERROR(@err_msg, @err_svt, @err_stt);
	RETURN;
END CATCH

@++ ;)

20/11/2015, 15h47
smilee44

Merci pour ta réponse !

Oui c'est vrai que l'appel d'une fonction scalaire est contre performant en ensembliste, je n'avais plus ce détail en tête. Par contre c'est normal qu'elle n'a pas de paramètre. Elle es juste la parcqu'on peut créer des factures de façons unitaires également , on appel donc cette fonction dans 2 cas différents. La génération du numéro es ainsi mutualisée

Par contre je ne vois pas trop ou tu veux en venir avec ton code. Tu ne fais pas d'insert :-)

Tu as juste fais un mix des 2 morceaux de code. Mais si je l’exécute j'ai toujours le même numéro de facture qui reviens.

Ah et merci pour la syntaxe des transactions dans un try catch. J'aime cette syntaxe !
24/11/2015, 10h18
aieeeuuuuu
Bonjour,

Je pense que vous devriez commencer par revoir la modélisation.
Votre numéro de facture contient plusieurs informations d'origines différentes, vous devriez donc avoir plusieurs colonnes (rien ne vous empêche de "recoller" ensuite les morceaux, dans une vue ou dans une colonne calculée).
En faisant cela, vous pourriez ensuite simplement utiliser une séquence pour résoudre très simplement votre problème.

ça rendrait en plus vos requêtes plus performantes, car ceci n'est pas sargable :
Code:

1 2 WHERE LEFT(numerofacture,6)= ...
24/11/2015, 11h29
SQLpro

Citation:

Envoyé par aieeeuuuuu

Bonjour,

Je pense que vous devriez commencer par revoir la modélisation.
Votre numéro de facture contient plusieurs informations d'origines différentes, vous devriez donc avoir plusieurs colonnes (rien ne vous empêche de "recoller" ensuite les morceaux, dans une vue ou dans une colonne calculée).
En faisant cela, vous pourriez ensuite simplement utiliser une séquence pour résoudre très simplement votre problème.

ça rendrait en plus vos requêtes plus performantes...

Plus encore, ceci est une abomination :
1) viol de la première forme normale (atomicité des données)
2) problématique de concurrence pour calculer le n° suivant provoquant systématiquement des collisions.

A +