Nested SQL Queries

[exeterfan]

Hello,

I'm currently trying to do a nested SQL query.

Breakdown of the steps I'm taking is:

Create SQL connection session1
Run SQL query 1 under session1
Loop through each row of session1

...
Other tasks
...
Create SQL connection session2
Run SQL query 2 (with variable gained from query 1 dataset) under session 2
Loop through each row of session2

...
Other tasks
...

End Loop 2
Disconnect SQL connection session2

End Loop 1
Disconnect SQL connection session1


Is this the right way of going about this? I'm encountering issues where the second loop doesn't seem to be reading from query 2 dataset plus I don't like having to have multiple connections to the same Database.

[forumsupport]

Hello,

Yes this should work. We can move command that connects to database using session2 at the beginning. Text format would appear as follows,

Create SQL connection session1
Create SQL connection session2

Run SQL query 1 under session1
Loop through each row of session1
...
Other tasks
...

Run SQL query 2 (with variable gained from query 1 dataset) under session 2
Loop through each row of session2
...
Other tasks
...
End Loop 2
End Loop 1

Disconnect SQL connection session2
Disconnect SQL connection session1

If it does not work, please upload the task so we can have a look and suggest viable solution.

Hope that helps.

[exeterfan]

Do you also need to set up a new connections if you want to run multiple Stored Procedures?

[TylerMitton]

Are you queries all on the same database? If so, you don't need to open a second connection at all. One connection can be used by many queries and stored procedures.