Looping through a result set, manipulating a the data in a stored procedure


I am trying to retrueve a result set and then I have to compaer some dat within that result set - I have to loop through each row - comparing the rows to each other  so lets say row ,1 has two columns, I need only column two and I need to compare it to row three and four.  I will also need to select pass a row back and forth to other stored procedures - I am able to do this in .NET Vb 2005 - but I am unsure how I can do it in a stored procedure - or actually a function.  I've just included a basic query to illustrate, thanks
CREATE FUNCTION .......................]
      @ID AS int

      SELECT @Em = COUNT(Em_Results.Comp_Number), Em_Results.Compound_Number,
      FROM Em_Results
      INNER JOIN Pha_Relts
            ON Pha_Relts.PID = Em_Results.PID
      INNER JOIN Test_Rlts
            ON Test_Rlts.ID = Pha_Relts.ID
      INNER JOIN B_Inv
            ON Test_Rlts.ID = Pha_Relts.ID
      WHERE TEST_RLTS.ID = 6705
      GROUP BY Em_Results.Com
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Can you be more specific about what you are trying to achieve.  Whenever someone says they want to loop through SQL it makes me think there should be a better way.  Please post some sample data and expected results from that data.

Your Sample Function doesn't make any sense to me, especially:
      INNER JOIN B_Inv
            ON Test_Rlts.ID = Pha_Relts.ID
which doesn't reference B_Inv at all.

Keep in mind that tables are unordered so comparing row 1 to row 3 doesn't make a lot of sense most of the time.
MosquitoeAuthor Commented:
Ah - Ok sorry - the examply I altered a bit so the actual names weren't there.  But the return from the query should (and does) return this below - You mention that tables are unordered, but if it is coming from a query, then wouldn't it be ordered by how you ordered your query?

1      3
2      3
3      3

So I know what 1, 2 , 3 means in terms of their compound number - Based on those values I klnow which record I am looking at, and simply want to compare the second columns values to each other to ensure they are all the same value.  Additionally, I then need to be able to pass this table back to the calling stored procedure .. in which case I would nee dto create it there, and then pass it to this function I suppose - That leads to the question, if you create a table, can you define it later and can you define it by a query?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
I'm still not sure I understand.  What is your initial data, what is your expected results. (Samples are fine, but the need to be a little more meaningful)

>You mention that tables are unordered, but if it is coming from a query, then wouldn't it be ordered by how you ordered your query?

Not guaranteed.  The optimizer may disorder the results for processing and then reorder for display purposes.  There is a big discussion about this going on at SQLServerCentral.com right now dealing with running totals.

It looks like you are trying to make sure that you have the same number of rows associated with each Compound Number associated with a Test Result, is that correct?

So you have
Select CompoundNum, Count(Results) as Numresults
From em_Results
Where TestID = @ID
group by CompoundNum

But now you want to make sure that NumResults is equal for all of them.  What do you want to do if it isn't?

To find all of the results that match the lowest compoundnum you could do something like:

Select CompoundNum, Count(Results) as NumResults
From em_Results
WHERE testID = @ID
Group by CompoundNum
Having Count(Results) = (Select Count(Results) From em_Results where testID = @ID and CompoundNum = (Select Min(CompoundNum) from em_results where testID = @ID))
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.