Error in a Mule application when calling a stored procedure in SQL

Question

I want to call a stored procedure but I'm getting an error:

Incorrect syntax near '@P0'

This is the stored procedure:

Here is the stored procedure call:

{CALL sp_Insert_RiskEvent((:householdId), (:loyaltyCard), (:userId), (:deviceId), (:eventDate), (:eventType))}

These are the parameters that I'm sending:

{
    'householdId': payload.householdId,
    'loyaltyCard': payload.loyaltyCard default null,
    'userId': payload.userId default null,
    'deviceId': payload.deviceId,
    'eventDate': payload.eventDate,
    'eventType': payload.eventType
}

Also I did a typeOf check and here is the response:

enter image description here

This is the stored procedure from SQL:

What is strange is when I add manually in the stored procedure the values the stored procedure is run without any issues.

Here are the answers:

1:

{
  "HouseholdId": 1234,
  "LoyaltyCard": 123456,
  "UserId": "6B29FC40-CA47-1067-B31D-00DD010662DA",
  "DeviceId": "1234455",
  "EventDate": "2023-02-27T04:00:00Z",
  "EventType": "test"
}

2.
   <flow name="post:\event" doc:id="6a6a8c9a-b430-44d5-ad6e-c31a3a703ff3" >
       <db:stored-procedure doc:name="Stored procedure" doc:id="155e219c-93b4-4a35-976b-2aaba060a8db" config-ref="Database_Config">
           <db:sql ><![CDATA[{CALL sp_Insert_RiskEvent((:HouseholdId), (:LoyaltyCard), (:UserId), (:DeviceId), (:EventDate), (:EventType))}]]></db:sql>
           <db:input-parameters ><![CDATA[#[{
   'HouseholdId': payload.householdId,
   'LoyaltyCard': payload.loyaltyCard default null,
   'UserId': payload.userId default null,
   'DeviceId': payload.deviceId,
   'EventDate': payload.eventDate,
   'EventType': payload.eventType
}]]]></db:input-parameters>
       </db:stored-procedure>
   </flow>
</mule>

3:

<dependency>            <groupId>com.microsoft.sqlserver</groupId>            <artifactId>mssql-jdbc</artifactId>            <version>6.2.2.jre8</version>        
</dependency>        
<dependency>            
<groupId>org.mule.connectors</groupId>            <artifactId>mule-db-connector</artifactId>            <version>1.13.5</version>            
<classifier>mule-plugin</classifier>        
</dependency>

4: 7.11.1

5: 6.2.2.jre8

Answer 1

Remove the parentheses around the parameters. They are redundant and may confuse the SQL parser.