Voir le flux RSS

hmira

[Actualité] SQL Server - Fonction scalaire T-SQL contrôle numéro SIREN ou SIRET

Noter ce billet
par , 17/01/2016 à 16h49 (1499 Affichages)
Je vous présente ci-dessous, une fonction scalaire SQL Server T-SQL, permettant de vérifier au niveau Serveur de base de données si des identifiants SIREN (9 chiffres) ou SIRET (14 chiffres) sont valides ou non.

Cette fonction s'inspire fortement de l'implémentation en T-SQL de l'algorithme de Luhn réalisée par SQLPro.
voir lien ci-dessous pour plus de détails :
http://blog.developpez.com/sqlpro/p7...algorithme_de_

L’algorithme de Luhn est utilisé pour le contrôle des identifiants SIREN ou SIRET dans le cas général, autres que La Poste et ses établissements,

Cette fonction implémente et tient compte également de l'algorithme particulier pour la vérification des identifiants SIREN ou SIRET de La Poste, et ce, depuis que La Poste a changé de statut et qu'elle est devenue une société anonyme.
La Poste est désormais dotée d'un seul SIREN : 356000000 et d'établissements SIRET : 356000000xxxxx

Ce nouvel algorithme de contrôle des identifiants SIREN ou SIRET, spécifique à La Poste, permet d'immatriculer jusqu'à 18000 établissements environ. Pour plus de détails :
http://blog.pagesd.info/post/2012/09...siret-la-poste

Voilà, le but est juste de partager cette fonction avec la communauté. Cette fonction pourra ainsi être utile à d'autres ou servir de base pour le développement d'autres fonctions de contrôle plus spécifiques.

Code SQL : 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
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
IF NOT EXISTS (SELECT *
           FROM dbo.sysobjects
           WHERE id = object_id(N'dbo.FC_U_IsValidSiret')
           AND xtype IN (N'FN', N'IF', N'TF')
           )
   EXEC   sp_executesql N'CREATE FUNCTION dbo.FC_U_IsValidSiret
( @pi_cSIRET VARCHAR(14) )
RETURNS BIT
AS
BEGIN 
RETURN NULL; 
END; ' 
GO
 
 
ALTER  FUNCTION dbo.FC_U_IsValidSiret
( @pi_cSIRET VARCHAR(14) )
RETURNS BIT
AS
/* ===========================================================================*/
/*                          dbo.FC_U_IsValidSiret                             */
/* ===========================================================================*/
/* Société            :                                                       */
/* Créateur           : Hamid MIRA                                            */
/* Date de création   : 17/01/2016                                            */
/* Version associée   : 1                                                     */
/* Objet              :                                                       */
/*   - Cette fonction scalaire SQL Server, permet de vérifier si un code SIREN (9 chiffes) */
/*   ou SIRET (14 chiffres) est valide ou non.                                             */
/*   - Cette fonction s'inspire fortement de l'implémentation en T-SQL de l'algorithme     */
/*     de Luhn réalisée par SQLPro. voir lien ci-dessous pour plus de détails :            */
/*     http://blog.developpez.com/sqlpro/p7829/langage-sql-norme/controle_de_coherence_par_algorithme_de_ */
/*   - Cette fonction tient compte également de l'algorithme particulier de La Poste */
/*     depuis qu'elle a changé de statut et qu'elle est devenue une société anonyme */
/*     dotée d'un seul SIREN : 356000000 et des établissement SIRET : 356000000xxxxx */
/*     Ce nouvel algorithme spécifique à La Poste permet d'immatriculer jusqu'à 18000 établissements environ */
/*     Pour plus de détails : http://blog.pagesd.info/post/2012/09/05/verifier-numero-siret-la-poste */
/* Appelée par        :                                                              */
/*                                                                                   */
/* Paramètres : @pi_cSIRET VARCHAR(14)  Code SIREN ou SIRET à vérifier la validité   */
/*				Valeur de retour de la fonction (BIT) :                              */
/*                   1 si le code SIREN ou SIRET est un code Valide                  */
/*                   0 si le code SIREN ou SIRET n'est pas un code valide            */
/* ===========================================================================*/
BEGIN
	-- Valeurs limites
	IF @pi_cSIRET IS NULL OR @pi_cSIRET = ''
	BEGIN
		RETURN NULL;
	END;
	DECLARE
	  @iLengthSiret SMALLINT;
 
	SET @iLengthSiret = LEN(@pi_cSIRET);
	IF @iLengthSiret <> 9 AND @iLengthSiret <> 14
	BEGIN
		RETURN 0;  -- c.à.d. False
	END;
 
	DECLARE
	  @I              TINYINT
	, @cChiffre       CHAR(1)
	, @iSumNombres    TINYINT
	, @iRang          TINYINT
	, @bIsSiretLaPoste BIT;
 
	DECLARE
	  @TableChiffre TABLE
	( iRang   SMALLINT
	, iNombre SMALLINT );
 
	SET @bIsSiretLaPoste = CASE
							  WHEN LEFT(@pi_cSIRET, 9) = '356000000' AND @iLengthSiret = 14
								THEN 1
							  ELSE 0
						  END;
	SET @I = @iLengthSiret;
	SET @iRang = 0;
	WHILE @I >= 1
	BEGIN
		SET @iRang = @iRang+1;
		SET @cChiffre = SUBSTRING(@pi_cSIRET, @I, 1);
 
		-- Vérification que la chaine ne contient que des chiffres
		IF @cChiffre NOT BETWEEN '0' AND '9'
		BEGIN
			RETURN 0;
		END;
		ELSE
		BEGIN
			INSERT INTO @TableChiffre(
			   iRang
			 , iNombre)
			VALUES
			(
			  @iRang
			, CAST(@cChiffre AS SMALLINT)
			);
		END;
		SET @I = @I-1;
	END;
	--  Cas général (autre que La Poste)
	IF @bIsSiretLaPoste = 0
	BEGIN
		-- Multiplication par deux des chiffres de rang paire
		UPDATE @TableChiffre
		  SET
			  iNombre = iNombre*2
		WHERE
		   iRang%2 = 0;
		-- Addition des chiffres pour les nombres strictement supérieur à 9
		UPDATE @TableChiffre
		  SET
			  iNombre = iNombre-9
		WHERE
		   iNombre > 9;
	END;
	-- Calcul du total des nombres de la table @TableChiffre
	SET @iSumNombres = ( SELECT
							SUM(iNombre)
						 FROM @TableChiffre
					   );
	RETURN CASE
		   --  Cas général (autre que La Poste)
			   WHEN @bIsSiretLaPoste = 0
				 THEN CASE
						  WHEN @iSumNombres%10 = 0
							THEN 1
						  ELSE 0
					  END
			   ELSE
		   -- Case particulier de La Poste
		   CASE
			   WHEN @iSumNombres%5 = 0
				 THEN 1
			   ELSE 0
		   END
		   END;
END;
GO

Exemple d'utilisation

Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
SELECT  '73282932000074' AS SIRET, dbo.FC_U_IsValidSiret('73282932000074') AS IsValid -- Code valide 
UNION  ALL 
SELECT '732829320',  dbo.FC_U_IsValidSiret('732829320') -- Code valide 
UNION ALL 
SELECT 	'356000000',  dbo.FC_U_IsValidSiret('356000000')  -- Code valide : La Poste 
UNION ALL 
SELECT 	'35600000066180',  dbo.FC_U_IsValidSiret('35600000066180')  -- Code valide : Un établissement de La Poste  
UNION ALL 
SELECT '39244740402112',  dbo.FC_U_IsValidSiret('39244740402112') -- Code invalide !
Résultat :
Code 'SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
SIRET	IsValid
73282932000074	1
732829320	1
356000000	1
35600000066180	1
39244740402112	0

A+
Hamid MIRA

Envoyer le billet « SQL Server - Fonction scalaire T-SQL contrôle numéro SIREN ou SIRET » dans le blog Viadeo Envoyer le billet « SQL Server - Fonction scalaire T-SQL contrôle numéro SIREN ou SIRET » dans le blog Twitter Envoyer le billet « SQL Server - Fonction scalaire T-SQL contrôle numéro SIREN ou SIRET » dans le blog Google Envoyer le billet « SQL Server - Fonction scalaire T-SQL contrôle numéro SIREN ou SIRET » dans le blog Facebook Envoyer le billet « SQL Server - Fonction scalaire T-SQL contrôle numéro SIREN ou SIRET » dans le blog Digg Envoyer le billet « SQL Server - Fonction scalaire T-SQL contrôle numéro SIREN ou SIRET » dans le blog Delicious Envoyer le billet « SQL Server - Fonction scalaire T-SQL contrôle numéro SIREN ou SIRET » dans le blog MySpace Envoyer le billet « SQL Server - Fonction scalaire T-SQL contrôle numéro SIREN ou SIRET » dans le blog Yahoo

Mis à jour 17/01/2016 à 17h22 par hmira

Catégories
SQL Server

Commentaires