Do you know how I can easily perform an 11-test (Elfproef)

The elfproef is a Dutch validation check that was used for bank account numbers before the introduction of IBAN and is still used for BSN (National identifier) and payment references in bank transaction. Here’s a sweet inline table valued function that will yield instant results. 1 million iterations of this take up almost no cpu time so it’s very fast.

The first part checks if the supplied value is numeric. If it is , we work with the supplied value, otherwise we work with the value 1 which will never yield a valid BSN. We cannot work with if/then constructs because we are working with an inline table function.

I used a JSON array for configuration because it makes the code easier to read. If you want to know how an elfproef works, here’s an explanation in Dutch: https://nl.wikipedia.org/wiki/Elfproef

Example usage: select BSN from Personen as PRS WHERE dbo.tvf_CheckElfProef(PRS.BSN) = 0

create or alter function dbo.tvf_CheckElfproef
(
    @Bsn varchar(9)
)
returns table
as
return
(
with Bsn as
(
    select
        Nr = cast(iif(isnumeric(@Bsn) = 1, @BsnNr, 1) as bigint)
),
Factoren as
(
    select
        Positie
      , Factor
    from openjson('
        [
            {"Positie": 1,"Factor": -1},
            {"Positie": 2,"Factor": 2},
            {"Positie": 3,"Factor": 3},
            {"Positie": 4,"Factor": 4},
            {"Positie": 5,"Factor": 5},
            {"Positie": 6,"Factor": 6},
            {"Positie": 7,"Factor": 7},
            {"Positie": 8,"Factor": 8},
            {"Positie": 9,"Factor": 9}
        ]
        ') with (Positie smallint, Factor smallint)
),
Totalen as
(
    select
        Totaal = sum((nr % power(10, Positie) / power(10, Positie - 1)) * Factor)
    from Factoren
    cross join Bsn
)
select
    ResultaatElfProef = Totaal % 11
from Totalen
)

create or alter function dbo.tvf_CheckElfproef

(

@Bsn varchar(9)

)

returns table

return

(

with Bsn as

(

select

Nr = cast(iif(isnumeric(@Bsn) = 1, @BsnNr, 1) as bigint)

Factoren as

(

select

Positie

, Factor

from openjson('

[

{"Positie": 1,"Factor": -1},

{"Positie": 2,"Factor": 2},

{"Positie": 3,"Factor": 3},

{"Positie": 4,"Factor": 4},

{"Positie": 5,"Factor": 5},

{"Positie": 6,"Factor": 6},

{"Positie": 7,"Factor": 7},

{"Positie": 8,"Factor": 8},

{"Positie": 9,"Factor": 9}

]

') with (Positie smallint, Factor smallint)

Totalen as

(

select

Totaal = sum((nr % power(10, Positie) / power(10, Positie - 1)) * Factor)

from Factoren

cross join Bsn

)

select

ResultaatElfProef = Totaal % 11

from Totalen

)