How to execute stored procedure in Dataflow by passing parameters?

Question

I have below rows generated by Join transformation in Dataflow within a pipeline.

Highlighted blue and orange color columns are joined based on 2 different JSON files in blob storage.

Now my query is that, how can I pass the above row data in to stored procedure as input parameters?

For example,
if I get 'cra5a_columnname' as 'CRM_Test_ORG' then I need to pass 'Test Australia' value as input parameter. Because the column name for this value is value of 'cra5a_columnname'.
Another Example:
if I get 'cra5a_columnname' as 'X_TERR_SALES_CHANNEL' then I need to pass 'Telesales Executives' value as input parameter. Because the column name for this value is value of 'cra5a_columnname'.

Thanks in advance, for any suggestions

Answer 1

Hi ,

Thanks for reaching out to Microsoft Q&A.

Steps to Pass Data as Parameters to a Stored Procedure

Prepare Your Data in Dataflow:

• Use transformations like Derived Column to create a dynamic column that holds the value to be passed as an input parameter. This column should pick the value based on the cra5a_columnname field. Example derived column expression, Let's call this new column InputParameter.

  case(cra5a_columnname == 'CRM_Test_ORG', CRM_Test_ORG, cra5a_columnname == 'X_TERR_SALES_CHANNEL', X_TERR_SALES_CHANNEL, 'Default_Value')

Sink Data to Temporary Storage (Optional):

• If your dataflow generates multiple rows and the stored procedure can only handle one row at a time, you can sink this data into a staging area like a SQL table or blob storage. Otherwise, directly pass the row data to the pipeline.

Add a Pipeline to Call the Stored Procedure:

• After the dataflow runs, add a ForEach Activity in the pipeline to iterate over each row.
• Inside the ForEach Activity, use a Stored Procedure Activity to pass the InputParameter and other required fields to the stored procedure.

Set Up Parameters in the Stored Procedure Activity:

• Inside the Stored Procedure Activity, set the parameters dynamically using the output from the dataflow or the staged data.
• For example:

  { "StoredProcedureParameter": "@input_parameter", "Value": "@activity('YourDataFlowActivityName').output.someColumn" }

Dynamic Mapping: Ensure your mapping dynamically matches the cra5a_columnname with the respective values to be passed.

Batch vs Row-by-Row Execution: If the stored procedure supports batch processing, you can pass all rows at once using a table-valued parameter. Otherwise, loop through rows using the ForEach activity.

Error Handling: Add appropriate error handling in case the stored procedure execution fails for certain rows.

Please feel free to click the 'Upvote' (Thumbs-up) button and 'Accept as Answer'. This helps the community by allowing others with similar queries to easily find the solution.