error Covnerting data type decimal to decimal

  

Hi,

I have an issue when calling a stored procedure that has some output values which are decimals.

I'm creating the parameters as decimal  and passing it to the Stored procedure. so I have   it implemented like this in the advanced query

The output parameters F1DT up till C2_Diff are decimals, the FPresent and FBpresent are boolean and the errorcode is an integer and the errorMessage is text.


Exec @ReturnValue = [DBMS].[dbo].[StoredProcedureName]
       @Id
      ,@F1DT OUTPUT
      ,@F2CR OUTPUT  
      ,@C1_Diff OUTPUT  
      ,@C2_Diff OUTPUT
      ,@FPresent OUTPUT
      ,@FBpresent OUTPUT
      ,@ErrorCode OUTPUT
      ,@ErrorMessage OUTPUT

select @F1DT, @F2CR, @C1_Diff, @C2_Diff, @FPresent ,@FBpresent, @ErrorCode, @ErrorMessage  


I'm pressuming that the data type DECIMAL in outsystems is using a lower precision than (20,2) , because the stored procedure output Variables I have set them to be 20,2 ,which is a requirement.

Did someone encounter this issue calling a stored procedure with decimal outputs? 

Furthermore, I have made the query variables to be text, so it will do a cast from decimal to nvarchar, because I'm using it for display purposes only,  but when the decimal is a negative value it will not cast it to nvarchar I will get an error : Error converting data type decimal to nvarchar.


any help or suggestion is much appreciated.


Alexei



Hello Alexei,

Have you seen this post before? It has some tips on how to execute SP...

https://www.outsystems.com/forums/discussion/14867/how-to-execute-stored-procedures/


João Heleno wrote:

Hello Alexei,

Have you seen this post before? It has some tips on how to execute SP...

https://www.outsystems.com/forums/discussion/14867/how-to-execute-stored-procedures/


Hi Joao,

Yes I have seen it and i used it as a reference , I'm calling other stored procedures without any problems, 

this is an issue because it is the first time I'm using using decimal as an output parameter. so I think there lies the problem do you have experience calling stored procedures that uses decimal as an output in outsystems?


obrigado,


Alexei


Solution

Update on my issue:


The way that I solved it was to let the stored procedure to return the values as a result set instead of using output parameters, I have tested this rigorously and if you want to use output variables they better be of the following type:

1. Text

2. Integer

3. Boolean ( bit 0 or 1)


The issue I was having is that after I encounter the error in converting decimal to decimal I decided to cast it as text ( so the output variable in outsystems was if type text ) and then display it in Outsystems. I only  needed it as a display, so I said no harm done here, but when you have a negative decimal value , you cannot cast it to a text value in Oiutsystems.

The best thing to do is to do it as described in step 4. Stored procedure returning a Result Set 

in the link provided by João above: 

https://www.outsystems.com/forums/discussion/14867/how-to-execute-stored-procedures/



 

Solution