none
ADF V2 - How to Check If Stored Procedure Output is EMPTY

    Question

  • I have a stored procedure activity followed by an if condition activity.

    I need to check if the output from the stored proc is empty or if the output count is 0.

    I have tried the following (knowing the result set is empty) but it either evaluates to FALSE (not empty) or it is a bad request.  It makes no difference if the activity is a stored procedure or a lookup.

    @bool(empty(activity('Stored Procedure').output))   >> always evaluates to not empty

    @bool(empty(first(array(activity('Stored Procedure').output))))  >> always evaluates to not empty

    @bool(empty(first(array(activity('Stored Procedure').output.value.columnName))))   >> bad request

    @bool(empty(first(array(activity('Stored Procedure').output.value))))   >> bad request




    • Edited by zl34 Thursday, July 12, 2018 4:40 AM
    Thursday, July 12, 2018 4:14 AM

All replies

  • Hi ml2018,

    In ADFv2, the output of the stored procedure activity is always not null and should be like: 

    {
        "effectiveIntegrationRuntime": "DefaultIntegrationRuntime (East US)",
        "executionDuration": 93
    }

    which doesn't include the processing result of your stored procedure.

    As a workaround, you could use a Lookup activity to get the output of your stored procedure, reference your stored procedure as the source dataset of the lookup activity, then use @{activity('MyLookupActivity').output to access it.

    Thursday, July 12, 2018 5:14 AM