Tworząc ostatnio bazę danych, wymaganiem była weryfikacja po stronie bazy danych poprawności wprowadzenia numeru PESEL i NIP.

Sprawa sprowadza się do wyliczenia reszty z dzielenia – w przypadku numeru PESEL lub sumy kontrolnej – w przypadku NIPu.

W treści wpisu załączam gotowe funkcje T-SQL do weryfikacji tych numerów.

Funkcja do walidacji numerów PESEL:

CREATE FUNCTION IsValidPesel
(
	@pesel nchar(11)
)
RETURNS bit
AS
BEGIN

	IF ISNUMERIC(@pesel) = 0
		RETURN 0

	DECLARE
		@weights AS TABLE
		(
			Position tinyint IDENTITY(1,1) NOT NULL,
			Weight tinyint NOT NULL
		)

	INSERT INTO @weights VALUES (1), (3), (7), (9), (1), (3), (7), (9), (1), (3), (1)

	IF (SELECT SUM(CONVERT(TINYINT, SUBSTRING(@pesel, Position, 1)) * Weight) % 10 FROM @weights ) = 0
		RETURN 1

	RETURN 0

END

Oraz funkcja do walidacji numerów NIP:

CREATE FUNCTION IsValidNip
(
	@nip nvarchar(15)
)
RETURNS bit
AS
BEGIN

	SELECT @nip = REPLACE(@nip,'-','')

	IF ISNUMERIC(@nip) = 0
		RETURN 0

	DECLARE
		@weights AS TABLE
		(
			Position tinyint IDENTITY(1,1) NOT NULL,
			Weight tinyint NOT NULL
		)
	INSERT INTO @weights VALUES (6), (5), (7), (2), (3), (4), (5), (6), (7)

	IF SUBSTRING(@nip, 10, 1) = (SELECT SUM(CONVERT(TINYINT, SUBSTRING(@nip, Position, 1)) * Weight) % 11 FROM @weights)
		RETURN 1

	RETURN 0

END

Teraz wystarczy jedynie dodać check constraint do odpowiednich tabeli:

ALTER TABLE Users ADD CONSTRAINT checkPesel CHECK (dbo.IsValidPesel(PESEL) = 1)
ALTER TABLE Users ADD CONSTRAINT checkNip CHECK (dbo.IsValidNip(NIP) = 1)