SQL Serveur et Proc pour clef RIB

Bonsoir,

Bruno REITER nous avait fait part de sa procedure stockée, tu dois pouvoir
facilement la transformer en fonction.

Nicolas.

-- -------------------------------------------------------------------------
----------------------------------------------------------------------------
-

--calcul clé RIB

declare @banque char(5)
declare @agence char(5)
declare @compte char(11)
set @banque = '20041'
set @agence = '01001'
set @compte = 'PQRSTUVWXYZ'
set @compte=upper(@compte)

set
@compte=replace(replace(replace(replace(replace(@compte,'A','1'),'B','2'),'C
','3
'),'D','4'),'E','5')
set
@compte=replace(replace(replace(replace(replace(@compte,'F','6'),'G','7'),'H
','8
'),'I','9'),'J','1')
set
@compte=replace(replace(replace(replace(replace(@compte,'K','2'),'L','3'),'M
','4
'),'N','5'),'O','6')
set
@compte=replace(replace(replace(replace(replace(@compte,'P','7'),'Q','8'),'R
','9
'),'S','2'),'T','3')
set
@compte=replace(replace(replace(replace(replace(replace(@compte,'U','4'),'V'
,'5'
),'W','6'),'X','7'),'Y','8'),'Z','9')

--select @compte
select 97 - (( convert(bigint,convert(varchar(2),convert(bigint,@banque +
@agence) % 97) + @compte) * 100) % 97 )

-- -------------------------------------------------------------------------
----------------------------------------------------------------------------
-


"Philippe Cazaux-Moutou" <philippe.cazaux@sflx.com> a écrit dans le message
de news:uGIpaEwdDHA.1832@TK2MSFTNGP09.phx.gbl...

Bonjour

qq un aurait il une proc stockee de controle de clef rib ??
car je voudrais faire une requete du style

Select * from compte
where clerib <> RoutineClerib(numbanque, numguichet, numcompte)

Attention y a des num de comptes qui peuvent contenir des lettrs (Poste,
CCP)

merci

CREATE PROCEDURE sp_verifierCompteBancaire
@BANQUE varchar(5),
@GUICHET varchar(5),
@COMPTE varchar(11),
@CLERIB varchar(2),

-- Param OUT Resultat
@RETOUR smallint OUTPUT,
@MESSAGE varchar(200) = null OUTPUT -- Message a afficher coté Client

AS
-- -------------------------------------------------------------------------
---------------------------------------------------------------------------
-- -------------------------------------------------------------------------
---------------------------------------------------------------------------
-- OBJET : Vérification de la validité d'un compte bancaire
-- -----------------
-- PARAM : Les infos du compte : Banque, Guichet, compte, clé RIB
-- -------------------------------------------------------------------------
---------------------------------------------------------------------------
-- -------------------------------------------------------------------------
---------------------------------------------------------------------------
DECLARE
@LONGCOMPTE varchar(23),
@COMPTECOMPLET varchar(21),
@NOUVEAUCOMPTE varchar(21),
@DEVISE varchar(5),
@ARRAY_ALPHABET varchar(26),
@ARRAY_CHIFFRES varchar(26),
@CARACTERE char(1),
@CHIFFRE char(1),
@I int,
@J int,
@POSLETTRE int,

@MODULO1 int,
@MODULO2 int,
@MODULO3 int,

@SBLOC1 varchar(9),
@SBLOC2 varchar(7),
@SBLOC3 varchar(7),

@SMODULO1 varchar(20),
@SMODULO2 varchar(20),
@SMODULO3 varchar(20),

@IBLOC1 INT,
@IMODULO1 INT,
@IMODULO2 INT,

@CONCATMODULO INT

-- vérifier que les paramètres sont bons

IF @BANQUE is null OR LEN(@BANQUE) <> 5
BEGIN
SELECT @MESSAGE = 'Numéro de Banque invalide '
SELECT @RETOUR = -1
RETURN @RETOUR
END

IF @GUICHET is null OR LEN(@GUICHET) <> 5
BEGIN
SELECT @MESSAGE = 'Numéro de Guichet invalide '
SELECT @RETOUR = -1
RETURN @RETOUR
END

IF @COMPTE is null OR LEN(@COMPTE) <> 11
BEGIN
SELECT @MESSAGE = 'Numéro de Compte invalide '
SELECT @RETOUR = -1
RETURN @RETOUR
END

IF @CLERIB is null OR LEN(@CLERIB) <> 2
BEGIN
SELECT @MESSAGE = 'Clé RIB invalide '
SELECT @RETOUR = -1
RETURN @RETOUR
END

SET @BANQUE = REPLACE(@BANQUE, ' ', 0)
SET @BANQUE = REPLACE(@BANQUE, '', 0)
SET @BANQUE = REPLACE(@BANQUE, '.', 0)
SET @GUICHET = REPLACE(@GUICHET, ' ', 0)
SET @GUICHET = REPLACE(@GUICHET, '', 0)
SET @GUICHET = REPLACE(@GUICHET, '.', 0)
SET @COMPTE = REPLACE(@COMPTE, ' ', 0)
SET @COMPTE = REPLACE(@COMPTE, '', 0)
SET @COMPTE = REPLACE(@COMPTE, '.', 0)

-- Début du test
SET @COMPTECOMPLET = @BANQUE + @GUICHET + @COMPTE
SET @NOUVEAUCOMPTE = ''

-- Dans un premier temps, on remplace toutes les lettres par des
chiffres
-- selon un tableau précis.
SET @ARRAY_ALPHABET = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
SET @ARRAY_CHIFFRES = '12345678912345678923456789'

SET @I = 1

WHILE (@I < 22)
BEGIN
SET @CARACTERE = SUBSTRING ( @COMPTECOMPLET , @I , 1 )
IF @CARACTERE = 'A' OR @CARACTERE = 'B' OR @CARACTERE = 'C' OR
@CARACTERE = 'D' OR @CARACTERE = 'E' OR @CARACTERE = 'F' OR
@CARACTERE = 'G' OR @CARACTERE = 'H' OR @CARACTERE = 'I' OR @CARACTERE 'J' OR @CARACTERE = 'K' OR @CARACTERE = 'L' OR
@CARACTERE = 'M' OR @CARACTERE = 'N' OR @CARACTERE = 'O' OR @CARACTERE 'P' OR @CARACTERE = 'Q' OR @CARACTERE = 'R' OR
@CARACTERE = 'S' OR @CARACTERE = 'T' OR @CARACTERE = 'U' OR @CARACTERE 'V' OR @CARACTERE = 'W' OR @CARACTERE = 'X' OR
@CARACTERE = 'Y' OR @CARACTERE = 'Z'
BEGIN
SET @POSLETTRE = CHARINDEX(@CARACTERE, @ARRAY_ALPHABET)
SET @CHIFFRE = SUBSTRING ( @ARRAY_CHIFFRES , @POSLETTRE , 1)

SET @NOUVEAUCOMPTE = @NOUVEAUCOMPTE + @CHIFFRE
END
ELSE
SET @NOUVEAUCOMPTE = @NOUVEAUCOMPTE + @CARACTERE
SET @I = @I + 1
END

SET @LONGCOMPTE = @NOUVEAUCOMPTE + @CLERIB

-- Maintenant que l'on n'a que des chiffre, il faut que effectuer
différents Modulo 97 par blocs
SET @SBLOC1 = LTRIM(RTRIM(SUBSTRING ( @LONGCOMPTE , 1 , 9 )))
SET @SBLOC2 = LTRIM(RTRIM(SUBSTRING ( @LONGCOMPTE , 10 , 7 )))
SET @SBLOC3 = LTRIM(RTRIM(SUBSTRING ( @LONGCOMPTE , 17 , 7 )))
SET @SBLOC1 = REPLACE(@SBLOC1, '.', 0)
SET @SBLOC2 = REPLACE(@SBLOC2, '.', 0)
SET @SBLOC3 = REPLACE(@SBLOC3, '.', 0)
SET @SBLOC1 = REPLACE(@SBLOC1, ' ', 0)
SET @SBLOC2 = REPLACE(@SBLOC2, ' ', 0)
SET @SBLOC3 = REPLACE(@SBLOC3, ' ', 0)

-- Modulo = signe % dans T-SQL
SET @IBLOC1 = CAST(@SBLOC1 AS varchar)
SET @IMODULO1 = @IBLOC1 % 97
SET @SMODULO1 = CAST(@IMODULO1 AS varchar)
SET @SMODULO2 = @SMODULO1 + @SBLOC2
SET @IMODULO2 = CAST(@SMODULO2 AS int) % 97
SET @SMODULO3 = CAST(@IMODULO2 AS varchar)
IF (ISNUMERIC(RTRIM(@SMODULO3) + RTRIM(@SBLOC3) ) = 1) and
(ISNUMERIC(RTRIM(@SMODULO3) ) = 1) and (ISNUMERIC(RTRIM(@SBLOC3) ) = 1)
SET @CONCATMODULO = CAST( ( RTRIM(@SMODULO3) + RTRIM(@SBLOC3) ) AS
varchar) -- ici modif
ELSE BEGIN
SELECT @MESSAGE = 'Compte bancaire invalide. Vérifiez tous les
paramètres.'
SELECT @RETOUR = -1
END

IF @CONCATMODULO % 97 <> 0
BEGIN
SELECT @MESSAGE = 'Compte bancaire invalide. Vérifiez tous les
paramètres.'
SELECT @RETOUR = -1
END
ELSE
SELECT @RETOUR = 0

RETURN @RETOUR


GO