PS_Control_int.sql

-- Procédure de contrôle des conversions INT
ALTER    PROCEDURE [dbo].[control_int]
    @source_table VARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @typed_table VARCHAR(100) = @source_table + '_typé';
    DECLARE @sql NVARCHAR(MAX);
    DECLARE @column_name VARCHAR(100);
    DECLARE @error_count INT = 0;
    DECLARE @max_errors INT = 5;

    -- Vérifier que la table typée existe
    IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = PARSENAME(@typed_table, 1))
    BEGIN
        RAISERROR('La table typée %s n''existe pas', 16, 1, @typed_table);
        RETURN -1;
    END
    
    -- Récupérer uniquement les colonnes INT
    DECLARE column_cursor CURSOR FOR
        SELECT c.name
        FROM sys.columns c
        JOIN sys.types tp ON c.user_type_id = tp.user_type_id
        WHERE c.object_id = OBJECT_ID(@typed_table)
          AND tp.name IN ('int', 'bigint', 'smallint')
        -- AND c.is_nullable = 0;  -- 0 = NOT NULL
         ;

    OPEN column_cursor;
    FETCH NEXT FROM column_cursor INTO @column_name;

    WHILE @@FETCH_STATUS = 0 AND @error_count < @max_errors
    BEGIN
        -- On logge uniquement 5 erreurs max par colonne
        SET @sql = N'
            INSERT INTO dbo.conversion_error_log (table_name, column_name, error_value, error_message)
            SELECT TOP (' + CAST(@max_errors - @error_count AS VARCHAR(10)) + N')
                ''' + @source_table + ''',                   
                ''' + @column_name + ''',                    
                ' + QUOTENAME(@column_name) + N' AS error_value,
                ''Valeur non convertible en INT  id de la ligne = '' + CAST(id AS VARCHAR(100))
            FROM ' + QUOTENAME(@source_table) + N'
            WHERE 
            TRY_CAST(' + QUOTENAME(@column_name) + ' AS INT) IS NULL

               ;


 --   --        ' + QUOTENAME(@column_name) + ' IS  NULL;'
        EXEC sp_executesql @sql;

        -- Mise à jour du compteur d'erreurs
        SELECT @error_count = COUNT(*) 
        FROM dbo.conversion_error_log 
        WHERE table_name = @source_table 
          AND column_name = @column_name;

        IF @error_count >= @max_errors
        BEGIN
            RAISERROR('Arrêt après 5 erreurs de conversion sur la colonne %s', 16, 1, @column_name);
            CLOSE column_cursor;
            DEALLOCATE column_cursor;
            RETURN;
        END

        FETCH NEXT FROM column_cursor INTO @column_name;
    END

    CLOSE column_cursor;
    DEALLOCATE column_cursor;

    IF @error_count = 0
        PRINT 'Aucune erreur de conversion INT détectée';
    ELSE
        PRINT 'Nombre total d''erreurs de conversion : ' + CAST(@error_count AS VARCHAR);

    -- Retourne les logs
    SELECT * FROM dbo.conversion_error_log WHERE table_name = @source_table;
END