Loop through a recordset and use the result to do another SQL select and return the results

sql, sql-server, stored-procedures

Solution

If you want looping through the records. You can do like:

--Container to Insert Id which are to be iterated
Declare @temp1 Table
(
  tempId int
)
--Container to Insert records in the inner select for final output
Declare @FinalTable  Table
(
  Id int,
  ProductId int
)

Insert into @temp1 
Select Distinct SomeId From YourTable

-- Keep track of @temp1 record processing
Declare @Id int
While((Select Count(*) From @temp1)>0)
Begin
   Set @Id=(Select Top 1 tempId From @temp1)

   Insert Into @FinalTable 
   Select SomeId,ProductId From ListOfProducts Where Id=@Id

   Delete @temp1 Where tempId=@Id
End

Select * From @FinalTable

Problem

I am completely new to stored procedure. This time, I need to create a stored procedure in MS SQL. Let's say I have the following table. ``` Table name: ListOfProducts -------------------------- SomeID, ProductID 34, 4 35, 8 35, 11 ``` How do I pass in a SomeID. Use this SomeID to select a recordset from table, ListOfProducts. Then loop through this record set. Let's say I pass in SomeID = 35. So, the record set will return 2 records with SomeID 35. In the loop, I will get ProductID 8 and 11, which will be used to do another select from another table. The stored procedure should return the results from the 2nd select. How can I do this in MS SQL stored procedure? Sorry, for this newbie question. Thanks for any help.

Original source