You have a Fabric tenant.
You are creating a Fabric Data Factory pipeline.
You have a stored procedure that returns the number of active customers and their average sales for the current month.
You need to add an activity that will execute the stored procedure in a warehouse. The returned values must be available to the downstream activities of the pipeline.
Which type of activity should you add?
According to MS Copilot: To execute the stored procedure and make the returned values available to downstream activities in your Fabric Data Factory pipeline, you should add a Stored procedure activity (option C). This activity is specifically designed to execute stored procedures in a database and can pass the output to subsequent activities in the pipeline.
Not sure in what scenario I would use Script activity over lookup, but considering we do not have Lookup as an answer here, we are left with Script as the answer. Stored Procedure activity does not return any values.
"When the stored procedure has Output parameters, instead of using stored procedure activity, use lookup activity and Script activity. Stored procedure activity does not support calling SPs with Output parameter yet." Literally on MS documentation
To back this, here is the source: https://learn.microsoft.com/en-us/azure/data-factory/transform-data-using-stored-procedure
Script activity offers flexibility and can execute stored procedures, it is more suited for scenarios where you need to combine multiple SQL operations or handle complex workflows. For this specific task, the Stored Procedure activity is more efficient and purpose-built.
Script or lookup
I was wrong https://learn.microsoft.com/en-us/fabric/data-factory/stored-procedure-activity