Newbie - Is this a job for a sproc?

I'm optimizing an Access mdb to run in front of a SS 2005 database. My approach is to move as much of the processing as possible out of Access and in to SQL Server.

I have a report uses an Access query as it's source. One field in that report is generated via a series of 4 or 5 sub-queries that are finally joined in to the report's source query.

I have enough knowhow to turn each individual Access query into a veiw inside SQL server, but I'm wondering if this wouldn't be better accomplished using a stored procedure?

Essentially, I'd need the sproc to open up a set of 50-60 records, loop through them until it finds the first record with certain criteria, then return a certain value as it's result. Finally, I need that vallue to be joined to a view that I will point to as the source for my report in Access.

Is it possible to do this with a sproc? Is this the right way to use a sproc?Essentially, I'd need the sproc to open up a set of 50-60 records, loop through them until it finds the first record with certain criteria,

Where is your data?|||Where is your data?
Thanks for the quick response..

Not sure what you're asking.. The set of data to be looped through is in a view. The set of data for the results to be attached to will also be a view.

Both views are based on tables residing in my database.

Do you need me to post a sample of my data?|||here's more detail on my data

The data to loop through is in a view called ActionItems.

Relevant fields are:
project_id, actionitem_id, actionitem_order, status_id, milestone_id

Projects have about 50-60 action items, each with their unique id. They're in order by actionitem_order. Action items are grouped into milestones - figure 6 items per milestone.

To get the return value, I want to loop through each action item until the first one with a status_id of 3 is reached. When that one is reached, the loop can be stopped and the prior milestone_id is passed on as the result of the function.

I want to attach the result of that function to another view joined on Project_id.|||Create this as a stored procedure that not only encorporates your views as nested subqueries, but which also find the first one with a value of 3 and returns it as a resultset.
Basically, if you are looping through records, you are doing something wrong. Its time to join the big boys and learn set-based processing.|||Create this as a stored procedure that not only encorporates your views as nested subqueries, but which also find the first one with a value of 3 and returns it as a resultset.
Basically, if you are looping through records, you are doing something wrong. Its time to join the big boys and learn set-based processing.
Ok, cool. I'll dump the "loop-through" concept.. I have it as a series of SELECT queries in Access anyway, so moving that into a sproc is more of a direct migration anyway..

Ok - you mention nested subqueries.. Do you have an example of a sproc that does this? One approach I've found in trying to figure this out myself is that of using temporary tables after each SELECT statement.. For some reason, this sounds like it would require more overhead than just chaining the SELECT statements together. Problem is, I don't know how to nest the statements together.. I'll do more research, but if you've got an example, I'd appreciate it..

Thanks!|||select * from (select * from (select * from SomeTable) Nest2) Nest1|||select * from (select * from (select * from SomeTable) Nest2) Nest1
That's what I was thinking, but I couldn't find any examples to confirm..

I'll work with it and post the followup questions that are sure to come..

Thanks!|||Ok, really quick on the followup, eh?

This is probably more of a SQL question, and maybe it should probably be obvious to me, but anyway..

In Access, my top-level query would look something like...

SELECT field1, field2, field3 FROM table INNER JOIN table ON query1.field1 = table.field1 GROUP BY query1.field2

So in the sproc I go:

SELECT field1, field2, field3 FROM table INNER JOIN table ON (Long SELECT Statement that makes up query1).field1 = table.field1 GROUP BY (Long SELECT Statement that makes up query1).field2

Obviously the issue is the fact that I have two very long SELECT statements to deal with (on after the JOIN, one after the GROUP BY). Not a big deal with a simple 2 level combination of queries, but my actual progression is 5 levels progression with 7 different queries, some of which have criteria and multiple joins.

That would make this a pretty large sproc with lots of syntax to verify and (in my case) probably get wrong.

If that's what it takes, no problem.. I just want to confirm that before I go on..|||No. You are heading down the wrong path here. You join subqueries in your statement as virtual tables with their own aliases. You should not be using them as criteria.
Plus, I highly doubt that your task requires five levels of subqueries.
Read the sections on subqueries in Books Online. Then review your task again. Don't try to build the entire sql statement at once, but instead write code that performs the lowest level subqueries efficiently and then build up from there.