SystemDB.Null returned by Stored Procedure but values are there

I am running few stored procedures given by business team which has 2 input field and 2 output fields (1. RetCode, 2.ErrMessage).
In case of successful runs Retcode=0 and ErrMessage= “No Error” when run through SQL Server Management Studio.
In UiPath Studio I have used UiPath.Database.Activities RunQuery command and is executing fine in terms of database update but on UiPath side it throws error if type of variable is set to something else than System.DBNull.
“Exception: System.InvalidOperationException: A value of type ‘System.DBNull’ cannot be set to the location with name ‘@RETCODE’ because it is a location of type ‘UiPath.Core.GenericValue’”

And when retCode and errMessage is made of type System.DBNull in UiPath the value in them are not captured and even in case of actual error.

I need the value is retcode as 0/1/2/3/4 and ErrMessage as “No Error”/“actual error message” which is actually returned in SSMS. Please help

Hi @SnehajitDas It is related to the data types used in UiPath for capturing the output values of the stored procedure. By default, the RunQuery activity returns output parameters as UiPath.Core.GenericValue objects, which may not be compatible with the data types expected by your stored procedure.

Hi @SnehajitDas,
This error occurs because you are trying to set a type System.DBNull value to a parameter named @RETCODE, which is expected to be of type UiPath.Core.GenericValue.

To resolve this error, you must ensure that the value you are trying to pass to the parameter is not null. You can do this by checking the value before passing it to the stored procedure.

Try to make changes in the store procedure for null values or apply try catch to that activity for this exception System.InvalidOperationException and there you can handle it.

Hope this helps


Thank you for the responses @ABHIMANYU_THITE1 and @FIROZ_RANGREZ .

However, here I am not trying to pass the value to the stored procedure but I am at the receiving end. As I mentioned there are 2 input and 2 output parameters I am only talking about the output parameters here. The inputs are working fine and the stored procedure is also executing fine in terms of database update.
One of the output parameter is @RETCODE which is getting some value as a result of execution of stored procedure. I am not able to retrieve that value by any means. This value will help me assess if the execution was perfect or if there was any error.

If the type is anything other than SystemDB.Null in UiPath for variable retcode it throws an error and if it is SystemDB.Null the value is not retirevable.

Hi @SnehajitDas ,

Could you let us know what is the version of Database activities used ?

Also Check this post and let us know if it was helpful for your case :