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 OUTPUTselect @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
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/
Hello Alexei,
Have you seen this post before? It has some tips on how to execute SP...
João Heleno wrote:
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,