Forum Discussion

Max's avatar
Max
New Contributor II
2 years ago

Set default value if stored procedure doesnt return anything

Hello,

I have a pipeline were im running a stored procedure in SQL to insert and update records. On update, If no records are found, the stored procedure snap doesn't display anything in the result set output view.

I also tried the SQL - execute snap and pasted the exec SP call in the SQL statement body of the snap. In this case if no results are returned i get this as output - "message: success."  Now, im returning something but this cant be used. I connected the router snap to the SQL execute and said  $AuditID != null then go here and $message != null then go there. I get errors saying Failure: $AuditID is undefined, if there are no results. If there are results returned from the SP then i get this error - Failure: $message is undefined. I have also tried using select @@ROWCOUNT at the end of the SP call to get a count and that is not working either.

All I need to do is provide a default value of "auditId not found" when the SP does not return any results. Can someone please help?

  

 

1 Reply

  • bojanvelevski's avatar
    bojanvelevski
    Valued Contributor
    2 years ago

    Hi Max,

    Because $AuditID is a field coming from the SP result, and will not be present when there's no data coming from the SP, I suggest you use the .hasPath() function. Your expression will look something like:

    $.hasPath('AuditID')

    The result is true/false based on the response of the SQL snap. 

    Hope this helps.

    Regards,

    Bojan