Passing a Parameter To A Stored Procedure

Help
, ,
1

Hi,

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
SELECT @pgPrevDay,@pgToday

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!

image
image1367×473 25.6 KB

1 Like
2

@mnefesoglu,
I think the @ symbol is not required in the Parameter name

3

it does not change

image
image706×266 14.2 KB

4

image
image731×332 13.6 KB

You have to give the parameter name in Name column
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

5

Thanks, but i already tried and did not work.

6

Hi ,
@ is not required and Please check the variable atDate (right side) contains the value.

7

Hello,

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

Best regards,
Julia

1 Like
8

@juliaF
I tried as you suggested but im encountering below exception.
kindly help me with that.

image
image1586×661 53 KB

Snippet for activities properties
image
image1601×575 43.6 KB

Regards,
Chinna

9

I guess, your procedure doesn’t have two parameters then. It should look something like that:

create PROCEDURE dbo.BL_checkOrderVolume
(
@ordernumber NVARCHAR(20),
@return_value INT OUTPUT
)
AS
BEGIN

SET @return_value = 1; --includes all the logic and somewhere set’s the output-value

end

10

@juliaF
Im sharing SQL query which is shared by Database team.
Snippet
image

11

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

12

@juliaF

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

13

I used ExecuteQuery activity for same SQL query but , but output is empty.

  1. image
    image1499×739 44.6 KB

    2.
    image
    image698×653 17.1 KB
14

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

  1. change the current procedure to get the value via output-parameter
  2. 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)
  3. create your own database-activities and implement the return-value
15

Did you resolved this issue? I have same issue so, need solution, If you can help me. Thanks

16

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.

1 Like