To facilitate native SQL activities you got store procedure support in Azure data factory (ADF) which is really helpful. When we work with store procedure, mostly we use input parameters, However; it also have output parameters, where a store procedure will return value like function. This article will describe how you can work store procedure with output parameters in ADF.
ADF have activity called 'store procedure' which is used for any store procedure you have written in SQL database. ADF also have lookup activity where you can use store procedure. The example we will go through will use Lookup activity to connect store procedure from a database.
Let's make a small Store Procedure (SP) with Output parameter:
CREATE procedure [ETL].[sp_testprocOutParm] (@Iambit bit OUTPUT) AS BEGIN SELECT 1 Iambit END
DECLARE @Iambit bit EXEC ETL.sp_testprocOutParm @Iambit OUTPUT;
Then you should able to see the store procedure under the dropdown list which you just created in the database. If you are not able to see the store procedure then it most likely with your access in ADF. Please remember, though you can execute the SP in SSMS doesn't mean you will have access the store procedure from ADF, talk with your portal Admin and find out if your user group have given enough permission to connect store procedure.
We need to create a variable as like below figure 1.4, please note that this variable is at pipeline scope. So make sure you do not click any activities rather the pipeline is selected.
Now, let's get to the pipeline and select set variable (as shown in figure 1.6). In the Set variable activity, please click variable tab then you will find recently created variable under the name, select it like the figure 1.6 is shown.