 # Thread: [RESOLVED] Is there such a thing as looping in SQL?

1. ## [RESOLVED] Is there such a thing as looping in SQL?

I have a pretty tedious SQL statement that seems amenable for looping. Here's a snippet of the SQL (I'm using MS Access 2007):

Code:
```SELECT
1*(y1-0) / 12,
2*(y1-0) / 12,
3*(y1-0) / 12,
4*(y1-0) / 12,
5*(y1-0) / 12,
6*(y1-0) / 12,
7*(y1-0) / 12,
8*(y1-0) / 12,
9*(y1-0) / 12,
10*(y1-0) / 12,
11*(y1-0) / 12,
y1,
y1 + 1*(y2 - y1),
y1 + 2*(y2 - y1),
y1 + 3*(y2 - y1),
y1 + 4*(y2 - y1),
y1 + 5*(y2 - y1),
y1 + 6*(y2 - y1),
y1 + 7*(y2 - y1),
y1 + 8*(y2 - y1),
y1 + 9*(y2 - y1),
y1 + 10*(y2 - y1),
y1 + 11*(y2 - y1),
y2,
y2 + 1*(y3 - y2),
y2 + 2*(y3 - y2),
y2 + 3*(y3 - y2),
y2 + 4*(y3 - y2),
y2 + 5*(y3 - y2),
y2 + 6*(y3 - y2),
y2 + 7*(y3 - y2),
y2 + 8*(y3 - y2),
y2 + 9*(y3 - y2),
y2 + 10*(y3 - y2),
y2 + 11*(y3 - y2),
y3
FROM hpaState;```
and it goes on like this for 2 more "stanzas". The sql will be stored as a query in a MS Access file and called from an Excel VBA subroutine which receives the record-set. But this pattern is just asking for an inner loop of 0 to 12 and an outer loop of 1 to 5.

I'm a SQL newbie, so I guess my questions would be:

1. Is there a looping facility in SQL?

2. Considering the repetitive usage of things like "y2 - y1" and "y3 - y2", is there a way to declare variables in SQL so that I can calculate the value once and use it in the SQL? I assume that each usage of "y2 - y1" requires 2 lookups.

3. What's the optimum (speedwise) way of writing such a SQL statement?

Of course, I can just grab the values y1, ..., y5 and return them to the calling Excel VBA program and do these calculation in Excel VBA. But I was told that the fastest way of doing things is to do as much as you can in SQL since SQL is very fast.

Thank you!
Pete  Reply With Quote

2. ## Re: Is there such a thing as looping in SQL?

1) Yes, there is a looping mechanism in SQL - at least there is in T-SQL ( the T means transaction)
2) Access uses it's own flavor of SQL, doesn't support the looping structure (as far as I am aware)
3) You wouldn't see any kind of performance gain at all... infact, you would probably experience a degradation in performance. SQL Is designed for Set Based instructions, like the one you have. I'd leave it alone.

-tg  Reply With Quote

access, loop, sql, vba #### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•

Featured