/*
CREATE FUNCTION check_PESEL (@PESEL char(11))
*/
ALTER FUNCTION check_PESEL (@PESEL char(11), @PLEC varchar(9), @DATAURODZ char(10))
RETURNS int
AS
BEGIN
    DECLARE @poprawny int = 0;
    --Wczytujemy dane z numeru pesel
    DECLARE @p_rok int = CAST(SUBSTRING(@PESEL, 1, 2) AS int);
    DECLARE @p_miesiac int = CAST(SUBSTRING(@PESEL, 3, 2) AS int);
    DECLARE @p_dzien int = CAST(SUBSTRING(@PESEL, 5, 2) AS int);
    DECLARE @p_plec int = CAST(SUBSTRING(@PESEL, 10, 1) AS int);
    DECLARE @p_suma int = CAST(SUBSTRING(@PESEL, 11, 1) AS int);

    DECLARE @a int = CAST(SUBSTRING(@PESEL, 1, 1) AS int);
    DECLARE @b int = CAST(SUBSTRING(@PESEL, 2, 1) AS int);
    DECLARE @c int = CAST(SUBSTRING(@PESEL, 3, 1) AS int);
    DECLARE @d int = CAST(SUBSTRING(@PESEL, 4, 1) AS int);
    DECLARE @e int = CAST(SUBSTRING(@PESEL, 5, 1) AS int);
    DECLARE @f int = CAST(SUBSTRING(@PESEL, 6, 1) AS int);
    DECLARE @g int = CAST(SUBSTRING(@PESEL, 7, 1) AS int);
    DECLARE @h int = CAST(SUBSTRING(@PESEL, 8, 1) AS int);
    DECLARE @i int = CAST(SUBSTRING(@PESEL, 9, 1) AS int);
    DECLARE @j int = CAST(SUBSTRING(@PESEL, 10, 1) AS int);
    DECLARE @k int = CAST(SUBSTRING(@PESEL, 11, 1) AS int);

    --Wczytujemy kolejne parametry przekazane do funkcji
    DECLARE @f_rok int = CAST(SUBSTRING(@DATAURODZ, 7, 4) AS int);
    DECLARE @f_miesiac int = CAST(SUBSTRING(@DATAURODZ, 4, 2) AS int);
    DECLARE @f_miesiac_offset int
    DECLARE @f_dzien int = CAST(SUBSTRING(@DATAURODZ, 1, 2) AS int);

    IF @f_rok > 1800 AND @f_rok < 1899
    BEGIN
        SET @f_miesiac_offset = 80;
    END
    ELSE IF @f_rok > 1900 AND @f_rok < 1999
    BEGIN
        SET @f_miesiac_offset = 0;
    END
    ELSE IF @f_rok > 2000 AND @f_rok < 2099
    BEGIN
        SET @f_miesiac_offset = 20;
    END
    ELSE IF @f_rok > 2100 AND @f_rok < 2199
    BEGIN
        SET @f_miesiac_offset = 40;
    END
    ELSE IF @f_rok > 2200 AND @f_rok < 2299
    BEGIN
        SET @f_miesiac_offset = 60;
    END

    SET @f_rok = CAST(SUBSTRING(@DATAURODZ, 9, 2) AS int);

    --Porownujemy dane z numeru pesel z parametrami funkcji
    DECLARE @valid int = 0;

    --Rok urodzenia
    IF @p_rok != @f_rok
    BEGIN
        SET @valid = @valid + 1
    END

    --Miesiac urodzenia
    IF @p_miesiac != @f_miesiac + @f_miesiac_offset
    BEGIN
        SET @valid = @valid + 1
    END

    --Dzien urodzenia
    IF @p_dzien != @f_dzien
    BEGIN
        SET @valid = @valid + 1
    END

    --Plec
    IF @PLEC = 'Kobieta'
    BEGIN
        IF @p_plec = 1 or @p_plec = 3 or @p_plec = 5 or @p_plec = 7 or @p_plec = 9
        BEGIN
            SET @valid = @valid + 1
        END
    END
    ELSE
    BEGIN
        IF @p_plec = 0 or @p_plec = 2 or @p_plec = 4 or @p_plec = 6 or @p_plec = 8
        BEGIN
            SET @valid = @valid + 1
        END
    END

    --Sprawdzamy kontrolke
    IF ((@a+3*@b+7*@c+9*@d+@e+3*@f+7*@g+9*@h+@i+3*@j+@k)%10) != 0
    BEGIN
        SET @valid = @valid + 1
    END

    IF @valid = 0
    BEGIN
        SET @poprawny = 1
    END
return @poprawny;
END;

GO

SELECT dbo.check_PESEL('53083006595', 'Meżczyzna', '30.08.1953');
SELECT dbo.check_PESEL('91052800717', 'Meżczyzna', '28.05.1991');