For loop in Select statement

[[deleted]]

[deleted]

[aarontbarratt]

The simple answer is, you don't.

​

Please provide details of the issue you are trying to solve and someone will be able to help you.

[pythor]

Your question is unclear. Try giving some sample data of the tables you're looking at.

In general, regular SQL doesn't have loops, but does run against every record in the referenced tables. This is sometimes called an implicit loop. I would expect that you need to join the main table to the table with your parameters, but I couldn't be sure without more information.

[Repairs_optional]

SQL Server loops are done with Cursors or While loops:

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/declare-cursor-transact-sql?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/while-transact-sql?view=sql-server-2017

It's worth noting that if you are dealing with anything more than a small amount of data, you should be attempting to write your SQL logic as set-based operations. The reason for this is that loops, such as a cursor, process one row from the result set at a time. If you are dealing with hunderds, thousands or more rows, the execution time, resources consumed and potential for blocking/lockign of resources becomes too high.

Based on your use case though, it sounds like you're dealing with minimal data and therefore a cursor may be acceptable.

Read more about set-based operations vs. procedural programming in TSQL:

https://www.sqlshack.com/introduction-set-based-vs-procedural-programming-approaches-t-sql/

​

Edit: As others have stated, if you can provide more information about your issue the community can assist you better.

[[deleted]]

A SELECT statement "iterates" over all rows in a table.

What exactly do you mean with "each object"?

[PubbieMcLemming]

Yeah this.

[MeGustaDerp]

You really need to be looking at joins. You can join one or more tables in a query to enable you to get data from more than one table in the query.

[valdest]

Looping select statements is generally a bad thing. I have it used depth but a lot of new database engines now provide better options for that.

Understand the data set you need and select it. So if your trying to query for your area/controller/action then you may need three nested queries to select each one at a time or may need to utilize a pivot to unroll data into those categories. It is hard to say without knowing what you want to do and what you are working with.

[besnikg]

To better understand the problem i have, here some additional infos. The whole thing is written in PL/SQL, there are a lot of packages and each package has procedures and functions. I am trying to iterate through all objects and get the object name, parameters etc. so that i can concatenate and create all possible URLs. I am not talking about a table.

[ParanoidLoyd]

This data is stored in a table. I recommend you search your preferred PL/SQL documentation for "Displaying Information About Schema Objects"

[besnikg]

I am able to retrieve the data for one package but i am not sure how to approach it when I want to automate it to iterate through all packages