Search code examples
sql-serverazurestored-proceduresazure-data-factory

Azure Data Factory - Null parameter in stored procedure activity


I have a stored procedure activity in my Azure Data Factory pipeline that recalls the following stored procedure on Azure Synapse:

CREATE PROCEDURE schema.procedure_name @portafoglio INT [...]

The activity in configured in order to use a variable (portafoglioElab), previously set in the pipeline:

enter image description here

My problem is how to pass a Null value as input parameter to the procedure via the Azure Data Factory Pipeline in the stored procedure activity.

I've tried by setting the variable portafoglioElab with null, "null", None, @coalesce(null) but I'm not finding the right value corresponding to SQL NULL.

This is the step of the pipeline where I set the variable:

enter image description here

It is a step inside the IF condition of the whole pipeline:

enter image description here

Indeed I'm getting this error:

Cannot create SQL Source. Please double check the connection string, stored procedure are set with correct format.
Error: The value of the property 'Value' is invalid for the stored procedure parameter 'portafoglio'.

Can please someone help me?

Thanks in advance


Solution

  • I've found a solutions with the dynamic content.

    It is not only possible to put dynamic content in the value of the parameter, but even the parameter itself cam be dynamic.

    enter image description here

    I solved after many tests with this value:

    @json(concat('{"portafoglio": {"value": ', variables('portafoglioElab'), '}}'))
    

    In this way I can handle null and not null values. Pay attention that the documentation is wrong, the content has to be a dictionary, not a string.