How to get value of Out Parameter of execute Query

Help
,
1

I have a store Procedure and its has 1 output parameter,
I want to access that value.
Please guide.

1 Like
2

I don’t think this is possible now.

I see that in UiPath.Database.Activities ExecuteQuery returns a datatable object.

Therefore the only solution is to rewrite your stored procedure so that it selects your output param.

1 Like
3

Thank you Badita.:slight_smile:

4

Hi, So what does the parameter (direction) Out is used to?
I am trying to achieve the same thing but cannot think that they build a parameter Out just to confuse people, and I want to have a dataTable and my Parameter in the Output of the activiy.

5

Hello, So I find my way to this, and Yes if the parameter is set to Out, you can Declare your parameter in your StoredProcedure with the attribute “out” and it will be available in UIPath :
eg
CREATE PROCEDURE [dbo].[MY_STOREPROC]
@myOutPut int out
AS
BEGIN
SET @myOutPut = 2
END

Then in UiPath you can call the storedProcedure with :
parameter : myOutPut
direction : out
value : myVar

4 Likes
6

Does someone actually tried this? I’m trying with the same steps that @nbouscarat said and I’m getting no value.

7

Hi @pablobalto, may it can help you :
CREATE PROCEDURE [dbo].[SP_MySTOREPROC]
@input bit,
@output bit out
AS
BEGIN
SET @output =@input
END

If you try this store proc it will return inside the output parameter, what you give in input one.
Then in Uipath :

image.png695×202 18 KB

8

Hi @nbouscarat, I already tried that and didn’t work. I don’t know if there’s something about the database connection. I’m using ORACLE.

9

Hi I tried this method to get a value after SQL insert. But it always return 0.

image
image988×549 25.4 KB

SP
Insert into [dbo].[Tbl_MappingDetail](Customer_EntityID,BusinessMessage_Uid,MessageFormat_Uid,Version_Uid,OriginType,FlowType,OriginSystem,Destinationtype,DestinationSystem,
Originformat,Destinationformat)Values(@Customer_EntityID,@BusinessMessage_Uid,@MessageFormat_Uid,@Version_Uid,(SELECT x.i.value(‘./SourceType[1]’,‘Varchar(100)’)FROM
@x.nodes(‘/form1/main/Body’) as x(i)),(SELECT x.i.value(‘./Messageflow[1]’,‘Varchar(100)’)FROM
@x.nodes(‘/form1/main/Body’) as x(i)),(SELECT x.i.value(‘./SourceName[1]’,‘Varchar(100)’)FROM
@x.nodes(‘/form1/main/Body’) as x(i)),(SELECT x.i.value(‘./TargetType[1]’,‘Varchar(100)’)FROM
@x.nodes(‘/form1/main/Body’) as x(i)),(SELECT x.i.value(‘./TargetName[1]’,‘Varchar(100)’)FROM
@x.nodes(‘/form1/main/Body’) as x(i)),(SELECT x.i.value(‘./SourceMessageFormat[1]’,‘Varchar(100)’)FROM
@x.nodes(‘/form1/main/Body’) as x(i)),(SELECT x.i.value(‘./TargetMessageformat[1]’,‘Varchar(100)’)FROM
@x.nodes(‘/form1/main/Body’) as x(i)))

Set @Mapping_id =@@IDENTITY

select @Mapping_id
Could see correct value populated in SQL result

10

Hello,
If you do a Select @Mapping_id, your result will be in the datatable “Result” I think. You need to init your @Mapping_id as an Out parameter inside your StoreProc.

regards

11

@badita,

In my scenario existing SP return multiple DT.

image
image796×320 6.23 KB

When i call the SP using execute Query activity only the first DT in the returned in output result. How i can store the required DT in the result?

12

@Rajasegar94 You are right. For now we don’t have a solution to this other then rewriting the query or executing multiple queries.

1 Like
13

@Badita, Thanks for the response. I will look to change the SP.
Can we except in upcoming version for SQL querying activity which stores the result as Dataset instead of DT to have a solution to this issue.

14

Hi @nbouscarat - I tried your method word by word, but still I couldn’t get the value in Output Parameter. I am using UiPath 2018.2 Community Edition.

16

You can manage to return only 1 datatable from your query :
SELECT problem, Solution, Keyword, Category FROM ([JOINED QUERY DISPLAYING YOUR RESULT])

Or you can put each result into an out variable from your activities. But Seems to not work anymore

17

Yep, you are right, I updated to the last version and don’t seems to work anymore…