Untitled

-- Table to store procedure names
DECLARE @ProcedureList TABLE (ProcedureName NVARCHAR(100));
INSERT INTO @ProcedureList (ProcedureName)
VALUES ('spAcGaNe'), ('spAnotherProcedure'), ('spThirdProcedure');

-- Create a table to store the results
DECLARE @Results TABLE (
    ProcedureID NVARCHAR(100),
    Result NVARCHAR(50)        -- Store result as NVARCHAR
);

-- Variables for dynamic execution
DECLARE @ProcName NVARCHAR(100);
DECLARE @Result NVARCHAR(50);  -- Adjusted for NVARCHAR result
DECLARE @SQL NVARCHAR(MAX);
DECLARE @Parameter INT = 11223;

-- Cursor to loop through each procedure
DECLARE ProcCursor CURSOR FOR
SELECT ProcedureName FROM @ProcedureList;

OPEN ProcCursor;
FETCH NEXT FROM ProcCursor INTO @ProcName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Dynamically execute the procedure
    SET @SQL = N'EXEC sf..' + @ProcName + ' @rm = @Parameter, @Result OUTPUT';
    EXEC sp_executesql @SQL, 
                       N'@Parameter INT, @Result NVARCHAR(50) OUTPUT', 
                       @Parameter, 
                       @Result OUTPUT;

    -- Insert the result into the results table
    INSERT INTO @Results (ProcedureID, Result)
    VALUES (@ProcName, @Result);

    FETCH NEXT FROM ProcCursor INTO @ProcName;
END;

CLOSE ProcCursor;
DEALLOCATE ProcCursor;

-- Retrieve the final results
SELECT * FROM @Results;