Apart from the four system databases, we already knew, Microsoft has introduced a new database…
As a DBA you are responisble for data consistency in the database, right?
And as a DBA you do not trust the front enders, right?
So you want to check their data, right?
A vital part of most system in Sweden is the “personnummer”.
To validate this you can use the function below.
Example SELECT dbo.TenModulo10UF(5511300115)
IF OBJECT_ID(N’dbo.TenModulo10UF’) IS NOT NULL DROP FUNCTION dbo.TenModulo10UF GO CREATE FUNCTION dbo.TenModulo10UF (@value bigint) RETURNS integer AS BEGIN DECLARE @s varchar(20) , @n integer , @i integer = 1 , @Return integer = 0 , @s2 varchar(20) = ” SET @s = CAST(@value as varchar(20)) IF DATALENGTH(@s) > 10 OR DATALENGTH(@s) < 10
BEGIN
SET @Return = 0 –error
GOTO RET
END
WHILE 0 = 0
BEGIN
SET @s2 = @s2 + CAST((CAST(SUBSTRING(@s,10 – @i, 1)as integer)* (1 + @i % 2)) as varchar)
SET @i = @i + 1
IF @i > 9
BREAK END SET @i = 1 SET @n = 0 WHILE 0 = 0 BEGIN SET @n = @n + CAST(SUBSTRING(@s2,@i,1) as integer) SET @i = @i + 1 IF @i > DATALENGTH(@s2)
BREAK END SET @n = @n % 10 IF @n = (10 – CAST(SUBSTRING(@s,10,1) as integer)) % 10 SET @Return = 1 –OK ELSE SET @Return = 0 –error RET: RETURN @Return END GO
You can also add the function as a check constraint:
CREATE TABLE dbo.Persons(PersonNummer bigint NOT NULL) GO ALTER TABLE dbo.Persons ADD CONSTRAINT CheckPnr CHECK (dbo.TenModulo10UF(PersonNummer)= 1) GO