T-SQL Càlcul del dígit de control. Comptes CCC i comptes IBAN.

He hagut de fer "comprovacions" massives dels dígits de control dels comptes bancaris. He fet la programació al servidor per accelerar el procés.

Aquí us deixo el codi que genera dígits de control tant per comptes IBAN com per comptes CCC.

Recordeu que els DC sempre els ha de proporcionar el banc. No s'hi val a calcular-los.

Create function IBANAllGenerateDC (@IBANxx00 CHAR(34) )
returns CHAR(2)
as
begin
declare @RetVal CHAR(2)
DECLARE @Pais CHAR(4)
DECLARE @s AS VARCHAR(200)
DECLARE @iModValue AS INT
DECLARE @iDigit AS INT
DECLARE @i AS INT

SET @s = @IBANxx00

SET @s = Replace(@s, 'A', 10)
SET @s = Replace(@s, 'B', 11)
SET @s = Replace(@s, 'C', 12)
SET @s = Replace(@s, 'D', 13)
SET @s = Replace(@s, 'E', 14)
SET @s = Replace(@s, 'F', 15)
SET @s = Replace(@s, 'G', 16)
SET @s = Replace(@s, 'H', 17)
SET @s = Replace(@s, 'I', 18)
SET @s = Replace(@s, 'J', 19)
SET @s = Replace(@s, 'K', 20)
SET @s = Replace(@s, 'L', 21)
SET @s = Replace(@s, 'M', 22)
SET @s = Replace(@s, 'N', 23)
SET @s = Replace(@s, 'O', 24)
SET @s = Replace(@s, 'P', 25)
SET @s = Replace(@s, 'Q', 26)
SET @s = Replace(@s, 'R', 27)
SET @s = Replace(@s, 'S', 28)
SET @s = Replace(@s, 'T', 29)
SET @s = Replace(@s, 'U', 30)
SET @s = Replace(@s, 'V', 31)
SET @s = Replace(@s, 'W', 32)
SET @s = Replace(@s, 'X', 33)
SET @s = Replace(@s, 'Y', 34)
SET @s = Replace(@s, 'Z', 35)

SET @s = RTRIM(SUBSTRING(@s,7,34)) + LEFT(@s,4) + '00'

IF ( ISNUMERIC(@s) = 1 )
begin
SET @iModValue = 0
SET @i = 1
WHILE ( @i <= Len(@s) ) begin SET @iDigit = CAST( substring(@s, @i, 1) AS INT ) SET @iModValue = ((@iModValue * 10) + @iDigit) % 97 SET @i = @i + 1 end SET @iModValue = 98 - @iModValue IF (@iModValue > 9)
set @Retval = CAST ( @iModValue AS CHAR(2) )
ELSE
set @Retval = '0' + CAST ( @iModValue AS CHAR(1) )
end
ELSE
set @Retval = '99'

return @Retval
end

GO

Create function [dbo].[CCCgenerateDC] (@Entitat AS CHAR(4), @Agencia AS CHAR(4), @Compte AS CHAR(10) )
returns CHAR(2)
as
BEGIN
--from: http://www.bulma.net/body.phtml?nIdNoticia=1396
declare
@RetVal CHAR(2),
@aPesos AS CHAR(10),
@iDC1 AS INT,
@iDC2 AS INT,
@iX AS INT,
@iDigit AS INT,
@iResto AS INT,
@sEntOfi AS CHAR(8)

SET @aPesos = '0137498625' --cal sumar-hi 1
SET @sEntOfi = @Entitat + @Agencia

SET @iDC1 = 0
SET @iX = 8
WHILE (@iX > 0)
begin
SET @iDigit = SUBSTRING(@sEntOfi,@iX,1)
SET @iDC1 = @iDC1 + ( CAST( substring(@aPesos, @iX+2, 1) AS INT ) + 1 ) * @iDigit
SET @iX = @iX -1
END
SET @iResto = @iDC1 % 11
SET @iDC1 = 11 - @iResto
IF (@iDC1 = 10) SET @iDC1 = 1
IF (@iDC1 = 11) SET @iDC1 = 0

SET @iDC2 = 0
SET @iX = 10
WHILE (@iX > 0)
begin
SET @iDigit = SUBSTRING(@Compte,@iX,1)
SET @iDC2 = @iDC2 + ( CAST( substring(@aPesos, @iX, 1) AS INT ) + 1 ) * @iDigit
SET @iX = @iX -1
END
SET @iResto = @iDC2 % 11
SET @iDC2 = 11 - @iResto
IF (@iDC2 = 10) SET @iDC2 = 1;
IF (@iDC2 = 11) SET @iDC2 = 0;

SET @retval = CAST ( @iDC1 AS CHAR(1) ) + CAST ( @iDC2 AS CHAR(1) )

return @Retval
end

cat: T-SQL Càlcul del dígit de control. Comptes CCC i comptes IBAN.
esp: T-SQL Cálculo del dígito de control. Cuentas CCC i IBAN.

El codi es proporciona 'as is'. No hi ha garantia i l'executaràs sobre la teva responsabilitat.
Sigues lliure d'utilitzar aquest codi. Llicència GPL2.
Part del codi està traduit des d'altres llenguatges de programació. La font està resenyada al codi.