I am using this example as a starting point to build an Insert query from SP into SQL:
My input values, however, are in a collection. Is it possible to use the query to grab a value from a specific index in the collection? For instance, in the linked example:
Solved! Go to Solution.
I do not think you can directly reference the collection values like you mentioned. here are a couple of ways I can think of do it.
1) You can use the "For Each" action to loop through each value in collection, store it in a variable, then use that in the insert action.
2) Use Collection Operation to extract the value at a specific index, store it in a variable, then use that in the insert action.
You might have to include either of the above in a LOOP action, until all "rows" in your collection are taken care of.
unfortunatelly it's not possible to reference collection element at given index position directly.
you will have to use 3 collection operation actions to GET values from collection and them to variables.
then use variables in SQL statement.
Thanks Marian and Prasanna!
Marian: That is what I did, which is fine for my proof of concept, but will be pretty unwieldy with my actual workflow which will require alot more than 3 variables.
Prasanna: #2 is what i did for my testing, but like i said above, doing this with 25 variables is not going to be a good plan. #1 I suppose could work but it would seem to mean that I would need to have the execute SQL action WITHIN each loop to pass the temporarily stored variable into SQL. I was really hoping to that only one operation executing at the end of my UDA to store all the data into the SQL data row.
so what about make it a task for SQL server to parse out single values?
you could create a stored procedure to which you hand over data collected in workflow and let it process/handle there.
you can hand over collection itself (semicolon separated list of values) if you have ensured collection contains a value for every column you need.
or you can turn it to some other structure (XML, column-value pairs...) and hand it over that way.