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

Hello,

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
)
RETURNS TABLE
AS
BEGIN
      DECLARE @Em TABLE;

      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
MosquitoeAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
MosquitoeConnect With a Mentor Author 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?
0
 
JimFiveCommented:
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.
--
JimFive
0
 
JimFiveCommented:
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))
0
All Courses

From novice to tech pro — start learning today.