Resolving Issues with Writing NULLs for Nullable UUIDs in SQL via Logic Apps

The Problem

I came across a problem while building a simple system integration that takes a single record payload from a source system and upserts that record into the SQL Database of the target system.

The issue I encountered was that the target table schema had nullable UUIDs. If the inbound payload is null for those specific fields, we need the SQL table to reflect that as null.

In the example I am about to demonstrate, you will observe that the SQL action throws an error when the target field is a nullable UUID and you pass a null value to it.

The error message you typically receive is:

Conversion failed when converting from a character string to uniqueidentifier.

Here is the simple logic app:

As you can see, a payload is received, parsed, and we check if the record already exists in the database. If it does, we update it; otherwise, we insert it.

This is the database table schema which includes a field called ParentTaskID, which is a nullable UUID.

The example payload which is going to be sent.

{
    "TaskID":"f0177ddf-907e-4acd-91d4-60ae68751243",
    "Name":"Task1",
    "ParentTaskID": null
}

You can see in this payload that we are sending a null for the ParentTaskID property.

This is what happens with the SQL actions when that null is passed to them

And to demonstrate that we did indeed pass a null, here is the body of the Parse Request action, which clearly shows the null value, and not a string or character that the SQL Action is complaining about.

Finally if we look at the SQL Actions inputs, you will see that this null has now magically become an empty string ""

The Solution

This is what the code behind the SQL action looks like. I have removed other values to focus only on the body object.

"Insert_new_Task": {
    "inputs": {
        "body": {
            "TaskId": "@body('Parse_Request')?['TaskID']",
            "Name": "@body('Parse_Request')?['Name']",
            "ParentTaskId": "@{body('Parse_Request')?['ParentTaskID']}"
        }
    }
}

At first, nothing really looks out of the ordinary, but if you look closely, you will see that the ParentTaskId property value has curly brackets wrapping the body expression.

So, because that looked odd when compared to the other properties, I decided to remove the curly brackets. And it looks like this now.

"Insert_new_Task": {
    "inputs": {
        "body": {
            "TaskId": "@body('Parse_Request')?['TaskID']",
            "Name": "@body('Parse_Request')?['Name']",
            "ParentTaskId": "@body('Parse_Request')?['ParentTaskID']"
        }
    }
}

Let's run the logic app again.

SUCCESS!
The SQL Action has executed successfully, and you can see that it now shows that it took a null as the input instead of an empty string.

So why did this work?

As I mentioned at the beginning of the solution, I am not entirely certain why this works. But I have a suspicion.

If you look at this documentation Reference guide to workflow expression functions in Azure Logic Apps and Power Automate and the section for implicit data type conversions, it mentions the following which I believe is saying that when the value, you are referencing, is wrapped in curly brackets, it will take that value and converts it into a string.

If this is indeed the reason for its behavior, then it's a bit frustrating because there doesn't seem to be a way to instruct it not to convert to a string when solely using the designer. You must access the code view to make these adjustments.