How to pass azure pipeline variable to mysql stored procedure query in look up activity

Question

I have to call a stored procedure in lookup activity of Azure Data Factory for mysql that takes azure pipeline variable as input but i dont know the exact syntax.

Like call stored_prpcedure("@variables('BAtchID')")

The variable is of string type

If anyone knows how exactly i can call it? Please do share.

Answer 1

You cannot directly use call stored_prpcedure("@variables('BAtchID')") in your query section of Look up activity.

• The query field expects a string value, when you use call stored_prpcedure("@variables('BAtchID')") directly, it will be parsed as is but not as a pipeline variable.

• Instead, you need to concatenate the query with pipeline variable using @concat() function in data factory.

• The following is a demonstration of how I used query field to execute stored procedure using dynamic content.

• You can use the dynamic content below to successfully achieve your requirement (replace stored procedure name and variable name)

@concat('call demo("',variables('value_to_pass'),'")')

• The above content will be parsed as call demo("Welcome") which is shown below (\ indicates escape character):

Note: The debug run in the above image failed because I don't have a stored procedure in mysql database.