[SOLVED] SQL Alternative to For Loop

SQL Alternative to For Loop

I'm working with SQL in Access. 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.

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.

Solution

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.