Hi,

I have an online ASP application that is using a stored procedure containing many queries. I need to add a loop query to the stored procedure for an additional query.

In my SQL database I have a table (ecastdata) containing a set of 30 similar fields (ClickThru1 to ClickThru30) for example:-

ClickThru1
ClickThru2
ClickThru3
blah blah etc.
blah blah etc.
ClickThru30

I need to have the same query looped for each of the 30 fields so I dont manually repeat the query 30 times!

The query I need for each field :-

BTW (# is the number for each field)
@strEcastID will be a fixed value.

How can I have above query in a loop so I dont have to repeat the query for each of the 30 fields ??

Please help

This is a strange relationship.
Normally when you have 30 odd fields relating to a particular field you would have them in a related table on a seperate row each, this would make your query very straght forward with a Group By clause.
In this sutuation a SUM will not work as you intended because it is meant to sum relational data.(Experts please confer)
In your situation your query should look something like below.

If the number of fields, ie 30, is set in stone then this approach is fine, if you may one day need 31+ fields, try a related table asap

======================================
They say, best men are molded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================

Thanks Rod, however I need the SUM for "each" ClickThru field, not for all the ClickThru fields combined together. I really don't want to have to write out the query 30+ times for the 30 fields.

Please advise

Hmmmm, ok, so there are many records with the same value for @strEcastID and you want to sum the clickThru fields in those records.

Select SUM(ClickThu1) as intClickThru1, SUM(ClikThru2)as intClickThru2, SUM(ClickThu3) as intClickThru1[3, ....... from ecastdata where EcastID = @strEcast.

Let me know if Im still off track, this seems pretty straight forward, can you post the table schema and a sample of what you would like the resulting recordset(s) to look like.

======================================
They say, best men are molded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================

Thanks Rod. I will try what you have suggested.

Form a dynamic query based on the syscolumns for your table. Then execute the dyn. query...you will get what you want...You need not change the query in future if additional fields are added