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.

Newbie

Hello everyone, am new to databases. Started of learning SQL on my own
and using SQL Server 2k... any pointers from ya'll abt my approach...
got familiar with some basic stuff abt SQL and SQL Server.. hope all of
you would gimme good suggestions..Mad wrote:
> Hello everyone, am new to databases. Started of learning SQL on my own
> and using SQL Server 2k... any pointers from ya'll abt my approach...
> got familiar with some basic stuff abt SQL and SQL Server.. hope all of
> you would gimme good suggestions..

Good suggestions about what?|||depends on what you know about suggesting someone asking you a
question, seems by your question that u needs suggestions as well...
thats nice... and abt " Good suggestions about what? " -- reead this
"am new to databases. Started of learning SQL on my own
and using SQL Server 2k... any pointers from ya'll abt my approach...
".. i requested for suggestions about hw to start things off...
something that you might want to put in thinking of how u started
off... take care ZeldorBlat|||On 16 Jan 2006 06:55:55 -0800, Mad wrote:

>Hello everyone, am new to databases. Started of learning SQL on my own
>and using SQL Server 2k... any pointers from ya'll abt my approach...
>got familiar with some basic stuff abt SQL and SQL Server.. hope all of
>you would gimme good suggestions..

Hi Mad,

Here are some good places to start:

http://www.amazon.com/exec/obidos/s...Go.x=12&Go.y=13

http://www.amazon.com/exec/obidos/s...Go.x=13&Go.y=15

Probably not what you hoped for, but the subjects of SQL and SQL Server
are just too broad and too complicated to be explained in a newsgroup
message.

--
Hugo Kornelis, SQL Server MVP|||Start with a book on RDBMS basics, so you have a foundation. You will
probably also have to learn how to think in a declarative progamming
language. I find that takes about a year of full-time coding and some
people never get it.

After that, get an intro book on SQL and apy attention to the syntax
quirks.

After you get thru that book, get a copy of SQL FOR SMARTIES for the
advanced programming tricks.|||> After you get thru that book, get a copy of SQL FOR SMARTIES for the
> advanced programming tricks.

But, be warned this book is based on standard SQL so a lot of the examples
won't work or there are better ways of coding using some of the built-in
functions of SQL Server.

Check out some of Itzik-Ben Gan's books which are more appropriate to SQL
Server and contain far better and more scalable examples.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1137459934.454621.116470@.o13g2000cwo.googlegr oups.com...
> Start with a book on RDBMS basics, so you have a foundation. You will
> probably also have to learn how to think in a declarative progamming
> language. I find that takes about a year of full-time coding and some
> people never get it.
> After that, get an intro book on SQL and apy attention to the syntax
> quirks.
> After you get thru that book, get a copy of SQL FOR SMARTIES for the
> advanced programming tricks.