Azure Data Factory Activity for Stored Procedure Output
Short answer
D Lookup
Question
Key Takeaways
Tests the distinction between activities: use Stored Procedure for execution only, but use Lookup when you need to capture and pass back the result of a query or stored procedure.
The Lookup activity is the correct choice for executing a stored procedure that returns a result set or scalar value, whereas the Stored Procedure activity does not support returning output values.
Common mistake: Candidates often select 'Stored Procedure' because it seems logically appropriate for running SQL code, failing to realize that this specific activity cannot return data to the pipeline.
Expert Analysis
Why the Answer Is Correct
The Lookup activity is designed to read data from a data store and return the results to the pipeline. According to Microsoft documentation, it can execute a query or a stored procedure and return the result as an object in the pipeline context. This allows subsequent activities to access the returned count.
Why the Other Options Are Wrong
The Stored Procedure activity (Option A) executes the procedure but does not return any output values or result sets to the pipeline; it is used solely for side effects. Get Metadata (Option B) retrieves file/folder metadata, not query results. Append Variable (Option C) modifies variables and does not execute database queries.
Community Comment Notes
Comments [1] and [2] correctly cite Microsoft docs stating that the Stored Procedure activity does not allow returning output. Comment [3] reinforces that Lookup is required for SPs with outputs or return values. Comment [5] incorrectly suggests Stored Procedure activity handles results, which contradicts official behavior.
Official Reference
https://learn.microsoft.com/en-us/azure/data-factory/control-flow-lookup-activity
This link opens the official vendor documentation in a new tab.
Exam Strategy
Remember that if a question requires 'returning' or 'capturing' data from a database operation, choose Lookup. If it only requires 'executing' without needing the result, choose Stored Procedure.
Community Votes
83% of anonymous learners picked answer D. Votes are pick records left by other test-takers — they are not the verified answer.
Discussion 5 contributions
Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.
From public community threads 5
-
Alongi 👍 2 Answered DThe Stored Procedure Activity does not allow to return an output, so Lookup is the correct one. Refer to: https://learn.microsoft.com/en-us/azure/data-factory/transform-data-using-stored-procedure
-
JamieMcD 👍 1 Answered DAlso ChatGPT: For your specific requirement: Retrieve the Number of Sales Invoices for the Current Date: If you simply need to call a stored procedure that returns the count and you want to use this count directly in your pipeline (e.g., for further processing or branching logic), the Lookup activity is preferable. If the stored procedure's primary purpose is to perform operations (e.g., insert/update/delete) and the output is not directly needed within the pipeline, the Stored Procedure activity is more appropriate. Given that your stored procedure returns the number of sales invoices and does not require input parameters, and assuming you want to use this count directly in the pipeline, the Lookup activity would be a good fit.
-
renan_ineu 👍 1 Answered D"Lookup activity reads and returns the content of a configuration file or table. It also returns the result of executing a query or stored procedure" - https://learn.microsoft.com/en-us/azure/data-factory/control-flow-lookup-activity#:~:text=returns%20the%20result%20of%20executing%20a%20query%20or%20stored%20procedure "When the stored procedure has Output parameters, instead of using stored procedure activity, use lookup acitivty and Script activity. Stored procedure activity does not support calling SPs with Output parameter yet" - https://learn.microsoft.com/en-us/azure/data-factory/transform-data-using-stored-procedure#:~:text=stored%20procedure%20activity%2C-,use%20lookup%20acitivty,-and%20Script%20activity
-
tadenet 👍 1 Answered AChatgpt: The Stored Procedure activity in Azure Data Factory is specifically designed to execute SQL stored procedures. It is the most suitable activity when you need to run a stored procedure and handle the results or output of that procedure. The other options do not fit the requirements: B. Get Metadata is used to retrieve metadata information from data stores, not to execute stored procedures. C. Append Variable is used to append a value to an existing variable, not for executing stored procedures. D. Lookup is used to retrieve a dataset from a data store but is not typically used to execute stored procedures that return a single value without parameters.
-
fahfouhi94 👍 1 Answered DWhen the stored procedure has Output parameters, instead of using stored procedure activity, use lookup acitivty and Script activity. Stored procedure activity does not support calling SPs with Output parameter yet.
Reader comments & corrections
No reader corrections yet — spotted an error or have a note? Share it below.