Untitled
-- Table to store results DECLARE @Results TABLE ( ProcedureID NVARCHAR(100), -- Procedure name Result BIT -- Boolean result (0 or 1) ); -- Procedure names DECLARE @ProcedureList TABLE (ProcedureName NVARCHAR(100)); INSERT INTO @ProcedureList (ProcedureName) VALUES ('spAATcao'), ('spAnotherProcedure'), ('spThirdProcedure'); -- Variables for execution DECLARE @ProcName NVARCHAR(100); DECLARE @ReturnValue INT; -- To store the returned 0 or 1 DECLARE @SQL NVARCHAR(MAX); DECLARE @Parameter INT = 11223; -- Input parameter for procedures -- 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 -- Construct dynamic SQL to execute the procedure and capture the return value SET @SQL = N'EXEC @ReturnValue = ' + @ProcName + ' @rm = @Parameter'; EXEC sp_executesql @SQL, N'@Parameter INT, @ReturnValue INT OUTPUT', @Parameter = @Parameter, @ReturnValue = @ReturnValue OUTPUT; -- Insert procedure name and result into the results table INSERT INTO @Results (ProcedureID, Result) VALUES (@ProcName, @ReturnValue); FETCH NEXT FROM ProcCursor INTO @ProcName; END; CLOSE ProcCursor; DEALLOCATE ProcCursor; -- Display the results SELECT * FROM @Results;
Leave a Comment