User Defined Data types as SQL Stored Procedure parameters (UPDATED)


#1

EDIT:Question changed as I am working through this issue and learning more of the exact problem

I am trying to call a stored Procedure but am getting the below conversion error when calling it from the API Docs section of the admin interface. The data type of the parameter is a user defined data type and I believe that is the problem but am unsure of how to fix it. I can execute the procedure from SQL Management studio with the same parameter so I know it is good. Any help is appreciated.

{
  "error": [
    {
      "context": null,
      "message": "Failed to call database stored procedure.\nCDbCommand failed to execute the SQL statement: SQLSTATE[42000]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Implicit conversion from data type nvarchar to varbinary is not allowed. Use the CONVERT function to run this query.",
      "code": 500
    }
  ]
}

Here is the parameters I am using:

{
    "params": [
   
    {
      "name": "idCompany",
      "param_type": "IN",
      "value": "0x0048F1F5A7DC5A2A431CBAD70270E59C5FA0"
    }

    ],
    "schema": {
     
    },
    "wrapper": ""
}

#2

@vanamp, what is the data type definition of the parameter? (What data type did you base your definition on?)


#3

Its not my database(and I am a SQL novice) but when I look in the user-defined data types it shows up as a varbinary(18)


#4

If you could provide a mockup of the stored procedure, that’d be helpful.

When you are using this procedure, what data type are you intending 0x0048F1F5A7DC5A2A431CBAD70270E59C5FA0 to be?


#5

The Stored Procedure it pretty lengthy so I am not sure how to even make a mock-up. There are multiple parameters that have the same user defined type I just posted the first one generating the error. I am a SQL beginner but here is what I know:

There is a table the has multiple categories. Each catagory has an ID of a user defined data type (similar to 0x0048F1F5A7DC5A2A431CBAD70270E59C5FA0).

My stored procedure requires this ID to create a record in another table. If I copy and paste this ID into the proper parameter in SQL managment studio the procedure runs fine. If I copy and paste the ID into a parameter using the API Docs I get the conversion error.

I don’t know exactly what the stored procedure is doing with the parameters but if I try to manual input all the parameters into the table I get an error in the software, so I am forced into using this stored procedure.

Hopefully This additional info. helps. If not I could possibly PM you the Stored procedure.