How to process output from stored procedure in Azure Data Factory

Question

I am trying to create pipeline in Azure Data Factory to validate data.
So, the logic is:
If the output of the stored procedure is 1, I will send email (using Azure Logic App) that we have an error.
If the output of the stored procedure is 0, do nothing.

This is detail of sp:

BEGIN  
 
  declare @LastDay datetime;   

  set @LastDay =(  
SELECT distinct top 1  
          cast(convert(char(11), [DateLoad], 113) as datetime) as DateLoad  
FROM [dbo].[Table]  
order by DateLoad desc  
)  
If @LastDay = (select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0))  
begin  
return 0  
end  

else  

begin  
return 1  
end  

END  

Here is current data flow (that I think it might be a solution, but I doubt it at this moment):
I know how to connect to Azure Logic App (after this process is completed), but I just want to know how to process the output of 1.

When I researched online, I see options of using of "Lookup" or " Set Variable" etc.

https://www.sqlservercentral.com/articles/reading-stored-procedure-output-parameters-in-azure-data-factory
https://microsoft-bitools.blogspot.com/2020/12/adf-snack-use-stored-procedure-output.html#:~:text=At%20the%20moment%20the%20Stored,but%20there%20is%20a%20workaround.&text=ADF%20can't%20handle%20output,row%20in%20your%20SELECT%20query.

This is a simple process, and I would like to get some feedback before I get deeper thinking. :)

Thank you.

Answer 1

Hi @KingJava,

Here are the steps that will help you.

For the look up you have to create a dataset as in below screenshot. (While create it will ask for SQL server linked service and the table name(optional))

Then in the next step you can select the proc that you are trying to call as in screenshot 2.

Debug will give you this result. the result key can be different in your case. That is the column header from your proc.

If your proc is returning values in integer, please create a variable with string as we don't have int support type for variable.

We will convert the output of the stored proc into string using string() function. please use the below code in the set variable value field. I have used result at the end you have to use you column name

    @string(activity('Call Proc').output.firstrow.result)  

Your second question is why not stored procedure activity
Reason : Stored procedure activity does not capture the result dataset. So we won't be able to read the output. Procedure activity can be used to call the procedure without waiting for response.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

HI @KingJava , Welcome to the Microsoft Q&A Platform, and thanks for your query.

You are correct we have to assign the output of the proc activity to a variable and then you can validate the condition in if activity. if the value is 1 then call logic app to send out communication and you can ignore the 0 condition.

Hope this help your question.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".