Using the SQL IN Operator

Help
,
1

Hi All,

I have been successfully using the Execute Query activity against a SQL database without any issues and I am comfortable with how it works and using arguments to pass in parameters using the @

However, I am trying to build a robot that first builds a string variable of IDs within a loop, and as part of the loop it adds in the ’ and , characters to essentially build the following string:

List_Of_IDs = ‘ID1’,‘ID2’,‘ID3’,‘ID4’,‘ID5’,

I then use a substring on the variable to remove the 1st character and the last 2 characters. This is to format the string variable so that I can then pass it into the following SQL

Select * from databasetable where ID in (@List_Of_IDs)

I am removing the last comma and I am also removing the first and last ’ (quotation character) because I know if I just pass a parameter in normally the quotation characters are automatically added.

I don’t get any errors when UiPath runs the query but the debugger shows that there is just no results returned.

If I copy out the string built as part of the List_Of_IDs following the removal of the comma and quotation characters and run this directly in SSMS and add in the first and last quotation then I get the results I am expecting.

Is anyone able to help as I am sure this is something to do with the formatting which is passing the SQL requirements but leading to 0 results returned.

2

Did you tried removing only the last character (,) ?
I don’t have access to database. So this is my guess.

3

Hi,

I have tried this and it does not seem to work. The only way I can get an IN statement to work is to include the values in the SQL meaning you cannot dynamically change them.

4

I ended up speaking with UiPath Support and the advised that the best way of dealing with this is to construct a String variable with the SQL in it and then pass this into the SQL Activity, rather than trying to use @Parameters

I have since done this at it works as expected