How to work with SQL Store procedure output parameters in Azure data factory

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

 

Let's see if the SP return expected value. But how do we execute the SP in SQL Server Management Studio (SSMS)? Please write below syntax you will find the outcome.

 

DECLARE @Iambit bit 

EXEC ETL.sp_testprocOutParm @Iambit OUTPUT;

You should have outcome like below fig 1.0

 

                           

                             Fig 1.0: Execution of output parameter in SSMS

 

So far so good, SP is created in the database and it's returned expected outcome. Now we need to move to ADF and connect this SP. Assuming you know how to create pipeline in ADF. In your pipeline get the Lookup activity as like figure 1.1.

 

Now, Go Settings of the Lookup activity and choose Store Procedure from Use query (as shown in figure 1.1)

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.

                           

                      Fig 1.1: Connect store procedure via Lookup in ADF

 

If you find out the store procedure in the list you are good to go for the next step. Next step is to import parameter by clicking the button import parameter (as like figure 1.2)

 

                       

                              Fig 1.2: import parameter

 

The import parameter will bring you all the parameters you have irrespective of input or output parameters, in this case output parameter will be shown.

 

 

How do we use this output parameter?

 

Since you have output parameter means you want to return some value from database and use in ADF. In this example return value will control the next activities in the pipeline. Let's store the return value into a variable.

 

So please drag and drop Set variable activity and connect with the Lookup as like below figure 1.3

 

                       

                                          Fig 1.3: Add Set variable

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.

                       

                                    Fig 1.4: Creating variable

While creating variable you will find there are three type of variables such as string, Boolean and Array. We have chosen Boolean type for the variable.

 

                       

                                  Fig 1.5: Creating variable

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.

 

                                   

                                             Fig 1.6: Choose the variable

 

To bind the value from returned store procedure to variable, you need write the expression under the 'value' of the variable as shown in figure 1.7

           

                          Fig 1.7: expression to hold return value

 

The expression to bind return value from Store procedure:

@activity('Lookup1').output.firstRow.Iambit

 

Let's explain what expression means:

 

We are selecting first row returned by the lookup and selecting the column which is returned by the store procedure, here column selected in the store procedure is Iambit.

 

Be Cautious:

Though variable as Boolean and store procedure returned the value as a bit, but I found error in the ADF. So I had to modify the expression where it explicitly convert return value to Boolean, like below which worked like a gem.

 

@bool(activity('Lookup1').output.firstRow.Iambit)

 

 

In summary, output parameter in store procedure a great facility when in need and happy to see that works in ADF.