USE [Risikomodell]
GO
/****** Object:  StoredProcedure [dbo].[pRiskModell_00_CompleteProcedures]    Script Date: 29.08.2025 15:16:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*********************************************************************************************************
-- Author:		Hans-Willi Jackmuth
-- Create date: 28.08.2025
-- Description:	Complete Procedures including short Logfile
*********************************************************************************************************/
ALTER PROCEDURE [dbo].[pRiskModell_00_CompleteProcedures]
AS
BEGIN
    -- Delete old tables
    IF OBJECT_ID('[dbo].[RiskModellCombinations8to6]', 'U') IS NOT NULL 
        DROP TABLE [dbo].[RiskModellCombinations8to6];
    IF OBJECT_ID('[dbo].[RiskModellInput]', 'U') IS NOT NULL 
        DROP TABLE [dbo].[RiskModellInput];
    IF OBJECT_ID('[dbo].[RiskModellResults]', 'U') IS NOT NULL 
        DROP TABLE [dbo].[RiskModellResults];

    -- Create log table if not exists
    IF OBJECT_ID('[dbo].[RiskModellLogfiles]', 'U') IS NULL
    BEGIN
        CREATE TABLE [dbo].[RiskModellLogfiles] (
            ProcName NVARCHAR(256),
            StartTime DATETIME,
            EndTime DATETIME,
            DurationMs INT,
            DurationFormatted NVARCHAR(200),
            ReturnValue INT
        );
    END

    -- Declare variables
    DECLARE @ProcName NVARCHAR(256);
    DECLARE @SchemaName NVARCHAR(128);
    DECLARE @StartTime DATETIME;
    DECLARE @EndTime DATETIME;
    DECLARE @DurationMs INT;
    DECLARE @return_value INT;
    DECLARE @FormattedDuration NVARCHAR(200);
    DECLARE @sql NVARCHAR(MAX);
    DECLARE @Minutes INT;
    DECLARE @Seconds INT;
    DECLARE @Millis INT;

    -- Cursor cleanup if already exists
    IF CURSOR_STATUS('global', 'proc_cursorComplete') >= -1
    BEGIN
        IF CURSOR_STATUS('global', 'proc_cursorComplete') = 1
            CLOSE proc_cursorComplete;
        DEALLOCATE proc_cursorComplete;
    END

    -- Declare and open cursor
    DECLARE proc_cursorComplete CURSOR FOR
    SELECT s.name AS SchemaName, p.name AS ProcName
    FROM sys.procedures p
    JOIN sys.schemas s ON p.schema_id = s.schema_id
    WHERE p.is_ms_shipped = 0
      AND p.name NOT IN ('pRiskModell_99_Caller', 'pRiskModell_00_CompleteProcedures')
    ORDER BY s.name, p.name;

    OPEN proc_cursorComplete;
    FETCH NEXT FROM proc_cursorComplete INTO @SchemaName, @ProcName;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @StartTime = GETDATE();

        SET @sql = N'EXEC @return_value = ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ProcName);
        EXEC sp_executesql @sql, N'@return_value INT OUTPUT', @return_value = @return_value OUTPUT;

        SET @EndTime = GETDATE();
        SET @DurationMs = DATEDIFF(MILLISECOND, @StartTime, @EndTime);

        SET @Minutes = @DurationMs / 60000;
        SET @Seconds = (@DurationMs % 60000) / 1000;
        SET @Millis = @DurationMs % 1000;

        SET @FormattedDuration = RIGHT('00' + CAST(@Minutes AS VARCHAR), 2) + ':' +
                                 RIGHT('00' + CAST(@Seconds AS VARCHAR), 2) + '.' +
                                 RIGHT('000' + CAST(@Millis AS VARCHAR), 3);

        INSERT INTO [dbo].[RiskModellLogfiles] (ProcName, StartTime, EndTime, DurationMs, DurationFormatted, ReturnValue)
        VALUES (@SchemaName + '.' + @ProcName, @StartTime, @EndTime, @DurationMs, @FormattedDuration, @return_value);

        FETCH NEXT FROM proc_cursorComplete INTO @SchemaName, @ProcName;
    END

    CLOSE proc_cursorComplete;
    DEALLOCATE proc_cursorComplete;

    -- Show results
    SELECT
        ProcName,
        StartTime,
        EndTime,
        DurationMs,
        DurationFormatted,
        ReturnValue
    FROM [dbo].[RiskModellLogfiles]
    ORDER BY ProcName;

END