SQL Alternative to For Loop

Question

I'm working with SQL in Access. I'm not much of a programmer, but I'm familiar with using VBA as well as SQL basics.

What I'm trying to accomplish is the equivalent in SQL of a for loop used in Visual Basic. I know this isn't "technically" possible in SQL and may not be the best method so I'm looking for advice. I know it can be accomplished for i=1,2,3, etc. by using unions and repeating the query for each value. This is inefficient and easily gets too complex to be evaluated.

Basically what I need is a method to query for i=1 then repeat and again output data for i=2 and so on. Using group by i is not an option because there are several subqueries involved as well.

Thanks in advance!

Solution 1

Without a lot of context, we can't provide a better answer, but you can accomplish something of a FOR loop in sql.

declare @ctr integer

set @ctr = 1

while @ctr < 1000
begin

--Do your logic

select @ctr = @ctr + 1

end

But this isn't a very efficient use of SQL. You should be able to write what you need without iterating over it. Think in SETS and you will get along better with your RDBMS.

Solution 2

OK what you need to accomplish is not really clear, I recommend posting your table structure and what you need to retrieve.

However, what you want "seems" to be doable using an IN statement.

select 
   * 
from 
   whatever 
where id IN (1,2,3)