CREATE TABLE S_COM.T_PATIENT_PTN
(PTN_NUM_INSEE_SEXE              CHAR(1),
 PTN_NUM_INSEE_DATE_NAISSANCE    CHAR(4),
 PTN_NUM_INSEE_COMMUNE           CHAR(5),
 PTN_NUM_INSEE_RANG              CHAR(3),
 PTN_CLEF_INSEE                  CHAR(2),
 PTN_NOM                         CHAR(32),
 PTN_PRENOM                      VARCHAR(25),
 CONSTRAINT CK_PTN_NNI 
    CHECK (S_ROUTINES.F_VALIDE_NNI(PTN_NUMERO_INSEE_SEXE,
                                   PTN_NUM_INSEE_DATE_NAISSANCE,
                                   PTN_NUM_INSEE_COMMUNE,
                                   PTN_NUM_INSEE_RANG,
                                   PTN_CLEF_INSEE)),
 CONSTRAINT CK_PTN_NOM 
    CHECK (S_ROUTINES.F_VALIDE_NOM(PTN_NOM, 'MAJUSCULES')),
 CONSTRAINT CK_PTN_PRENOM 
    CHECK (S_ROUTINES.F_VALIDE_NOM(PTN_NOM, 'INITMAJ')));

/* 

--> syntaxe pour SQL Server des foutions de validation

CREATE FUNCTION S_ROUTINES.F_VALIDE_NNI (@NNI_SEXE CHAR(1),
                                         @NNI_DN   CHAR(4),
                                         @NNI_CMN  CHAR(5),
                                         @NNI_RANG CHAR(3),
                                         @NNI_CLEF CHAR(2))
RETURNS BIT
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
   IF @NNI_CMN LIKE '2A%' 
      SET @NNI_CMN = REPLACE(@NNI_CMN, '2A', '19');
   IF @NNI_CMN LIKE '2B%' 
      SET @NNI_CMN = REPLACE(@NNI_CMN, '2A', '18');
   IF @NNI_CLEF = 97 - CAST(CONCAT(@NNI_SEXE, @NNI_DN, @NNI_CMN, @NNI_RANG) AS DECIMAL(38,0)) % 97
      RETURN 1
   RETURN 0;
END
GO

CREATE FUNCTION S_ROUTINES.F_VALIDE_NOM(@NOM VARCHAR(255), @STYLE CHAR(10))
RETURNS BIT
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
   IF @STYLE NOT IN ('MAJUSCULES', 'MINUSCULES', 'INITMAJ')
      RETURN NULL;
   IF @STYLE = 'MAJUSCULES' 
      AND @NOM = UPPER(@NOM) COLLATE Latin1_General_CS_AI 
      RETURN 1;
   IF @STYLE = 'MINUSCULES' 
      AND @NOM = LOWER(@NOM) COLLATE Latin1_General_CS_AI 
      RETURN 1;
   IF @STYLE = 'INITMAJ' 
   BEGIN
      DECLARE @I TINYINT = 1, @INITCAP VARCHAR(255) = LOWER(@NOM), @TC CHAR(1), @PC CHAR(1)
      WHILE @I <= LEN(@NOM)
      BEGIN
         SET @TC = SUBSTRING(@NOM, @I, 1)
         SET @PC = CASE 
                      WHEN @I = 1 
                         THEN ' '
                      ELSE SUBSTRING(@NOM, @I - 1, 1)
                   END;
         IF @PC COLLATE Latin1_General_CI_AI NOT BETWEEN 'A' AND 'Z'
            IF @PC <> '''' OR UPPER(@TC) <> 'S'
               SET @INITCAP = STUFF(@INITCAP, @I, 1, UPPER(@TC))
         SET @I = @I + 1;
      END
      IF @INITCAP = @NOM
         RETURN 1;
   END
   RETURN 0;
END
GO

*/




