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

Veli-Jussi Raitila 386

How do I pass a (potential) NULL value to a stored procedure using the InApp SQL Server 'Execute stored procedure' action in Azure Logic Apps Standard?

The parameter is of type uniqueidentifier. Invoking the procedure directly using Transact-SQL works just fine. However, within a workflow I get an exception:

{
  "code": "ServiceProviderActionFailed",
  "message": "The service provider action failed with error code 'ServiceOperationFailed' and error message 'Object reference not set to an instance of an object.'."
}

I would not want to work around such a basic functionality.

MuthuKumaranMurugaachari-MSFT 22,336 Reputation points

2024-02-08T20:01:39.8566667+00:00

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?

Veli-Jussi Raitila 386

It makes no difference how I pass it in. However, this is a short example to reproduce it without implicit casting to a string: Screenshot 2024-02-09 at 9.07.39

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.

MuthuKumaranMurugaachari-MSFT 22,336 Reputation points

2024-02-13T15:32:00.2533333+00:00
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://learn.microsoft.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.
Veli-Jussi Raitila 386 Reputation points

2024-02-14T07:07:19.8633333+00:00

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.
MuthuKumaranMurugaachari-MSFT 22,336 Reputation points

2024-02-14T16:12:05.5233333+00:00

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.
Steve Mendoza 6 Reputation points

2024-10-03T21:36:40.7933333+00:00

@MUTHUKUMARAN MURUGAACHARI was there any update to this? I'm experiencing the same issue but with a parameterized query.

Share via

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

Your answer