Cannot pass a NULL value as a parameter of type 'uniqueidentifier' in a stored procedure

Veli-Jussi Raitila Thanks for posting your question in Microsoft Q&A. Can you share the designer view of how you are passing the null value to the stored procedure?

It makes no difference how I pass it in. However, this is a short example to reproduce it without implicit casting to a string:

In code:

{
  "type": "ServiceProvider",
  "inputs": {
    "parameters": {
      "storedProcedureName": "NullException",
      "storedProcedureParameters": {
        "SomeId": "@null"
      }
    },
    "serviceProviderConfiguration": {
      "connectionName": "sql",
      "operationId": "executeStoredProcedure",
      "serviceProviderId": "/serviceProviders/sql"
    }
  },
  "runAfter": {}
}

The procedure:

CREATE PROCEDURE [dbo].[NullException]  
   @SomeId uniqueidentifier
AS
BEGIN
    SELECT @SomeId
END
GO

Invoking the procedure directly in SQL with a NULL value has no issue - as expected.

Veli-Jussi Raitila Thanks for your patience and sharing the details. Unfortunately, you cannot pass NULL value that way (currently not supported). As per doc: https://dori-uw-1.kuma-moon.com/en-us/connectors/sql/#execute-stored-procedure-(v2), the parameters are dynamic, and it would fail to convert NULL from nvarchar (default type) to uniqueidentifier type.

Instead, you can set default value as NULL in Stored Procedure as below for the parameter and pass parameter as null. In this way, the parameter is rightly considered as NULL and should work without exception.

Stored Proc:

CREATE PROCEDURE dbo.MyTestStoredProc
    @MyParameter uniqueidentifier = null
AS
BEGIN
    -- Your SQL statements here
    -- You can use @MyParameter in your queries
    -- For example:
	IF @MyParameter IS NULL
	BEGIN
		SELECT 1
	END
	ELSE
	BEGIN
    	SELECT 100
	END
END;

Logic App:

"actions": {
            "Execute_stored_procedure_(V2)": {
                "type": "ApiConnection",
                "inputs": {
                    "host": {
                        "connection": {
                            "referenceName": "sql"
                        }
                    },
                    "method": "post",
                    "body": {
                        "MyParameter": null
                    },
                    "path": "/v2/datasets/@{encodeURIComponent(encodeURIComponent('default'))},@{encodeURIComponent(encodeURIComponent('default'))}/procedures/@{encodeURIComponent(encodeURIComponent('[dbo].[MyTestStoredProc]'))}"
                },
                "runAfter": {}
            }
        }

If you have a use case scenario where this solution doesn't help, please elaborate more or feel free to submit your idea/feedback via https://feedback.azure.com/d365community/forum/cb47c115-7926-ec11-b6e6-000d3a4f032c and others with similar interest can upvote it too.

Also, observed that you are using V1 action which is deprecated and suggest you move it to V2 action. Refer https://azure.microsoft.com/en-us/updates/sql-server-connector-s-v1-actions-and-triggers-will-be-retired-on-31-march-2024-transition-to-sql-server-connector-s-v2-actio/ for more info.

I hope this helps and let me know if any questions.

I will try this out when I have the time, but I do not think that will work.

The exception seems to come from the closed-source SDK employed by the Logic Apps connector, and not from SQL server. As I said, invoking the stored procedure in direct SQL works just fine and is able to accept a NULL value. There is no casting problem within SQL. And hence I cannot see how adding conditionals on the SQL-side would help.

Second, I am not using the "V1 action" of the connector. I am using the InApp connector available on the Logic Apps Standard (which I also told I was using) - as is apparent from code (mention of serviceProviderConfiguration etc.). It only has a single version.

Third, documentation for the connector - the little there is - can be found here. The (closed-source) SDK accepts parameters as an object. And this seems more like a bug in the InApp connector rather than an improvement request. As there is no reason I can think of why this should not just work. The error is a result of a NullReferenceException being thrown, which should not happen and does not come from the database.

Veli-Jussi Raitila Sorry, I was checking the managed connector for SQL Server and able to reproduce this issue with the built-in connector. I agree this is on the connector side (not SQL Server) and reaching out to our product team with the feedback. Will get back to you once I hear from them. My apologizes.