I have a procedure that gets yesterday’s working date. And it uses today’s date to get previous date.
DECLARE @pgToday SMALLDATETIME
DECLARE @pgPrevDay SMALLDATETIME
EXEC R_Get_Today @pgToday OUTPUT
EXEC Get_Next_TH_Workday 1,1,@pgToday,@pgPrevDay OUTPUT
it works like that and returns 2 dates; but in my uipath process i could not get the result.
In my process, i have an activity connect and execute query. Firstly, i tried to execute todays date procedure, but it does not work. Do you have any idea that can help me to solve this problem?
Thank you so much!
I think the @ symbol is not required in the Parameter name
You have to give the parameter name in Name
the direction of the parameter is a type of I/O
Type of the parameter in the Type column
passing a value to the parameter
Thanks, but i already tried and did not work.
@ is not required and Please check the variable atDate (right side) contains the value.
if you want to get values form the stored-procedure via output-parameters, you need to use ExecuteNonQuery instead of ExecuteQuery.
ExecuteQuery only returns a Datatable → result from the procedure
ExecuteNonQuery returns how many rows are affected in the output-datatable and the output-parameters.
You also need to change the Direction of the output-parameter to “Out” in UiPath
Snippet for activities properties
I tried as you suggested but im encountering below exception.
kindly help me with that.
I guess, your procedure doesn’t have two parameters then. It should look something like that:
create PROCEDURE dbo.BL_checkOrderVolume
@return_value INT OUTPUT
SET @return_value = 1; --includes all the logic and somewhere set’s the output-value
Im sharing SQL query which is shared by Database team.
A you can see, you do not return the @return_value via parameter but via the select-statement.
In my opinion that’s really ugly - why not use an output-parameter?
If you really want to use the SQL-statement like that you need to use ExecuteQuery and remove the Return-value-parameter in the parameter-list of UiPath. You will get your return_value via Output->Datatable in ExecuteQuery
Thanks for your suggestion
If this query need to execute using ExecuteQuery activity, then in properties command type is text format yes… then how can i write that query in a text format
I used ExecuteQuery activity for same SQL query but , but output is empty.
ahhh, I can see it now.
Your return_value will be an actual return-value in the procedure - I thought you will return the value via select.
I don’t know if this code is up to date, but it seems like the ExecuteNonQuery does implement a return-value (ExecuteNonQuery_GitHub). Maybe the datatable returns your return-value in the affectedRecords-output when using executenonquery…
Have you tried that one?
I’ve never worked with the return-values myself but I guess (i do not know!) it is not implemented in the database-activities if this one above does not work.
Then you have multiple options
- change the current procedure to get the value via output-parameter
- create a procedure to call your procedure which returns the return-value via output-parameter (it’s ugly but it would do what you need)
- create your own database-activities and implement the return-value
Did you resolved this issue? I have same issue so, need solution, If you can help me. Thanks
Hi there, if you want to return data, please use Execute Query. If you only want to have the affected result returned then use Execute Non-Query.
Please may you post a screenshot of your error @pari_patel, I’d like to see your properties and what the error is.
I’ve attempted this with a user defined table type which I’ve detailed in my blog post which works quite well.