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

Question

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.

Answer 1

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

Answer 2

There is probably no point in writing an explicit loop if you don't need to preform some action on the products that can't be done on the whole set. SQL Server can handle stuff like this much better on its own. I don't know what your tables look like, but you should try something that looks more like this.

CREATE PROC dbo.yourProcName 
   @SomeID int
AS 
BEGIN
    SELECT
        P.ProductId,
        P.ProductName
    FROM
        Product P
        JOIN 
            ListOfProducts LOP
            ON LOP.ProductId = P.ProductId
    WHERE
        LOP.SomeId = @SomeID 
END

Answer 3

I had to do something similar in order to extract hours from a select resultset start/end times and then create a new table iterating each hour.

DECLARE @tCalendar TABLE
(
    RequestedFor VARCHAR(50),
    MeetingType VARCHAR(50),
    RoomName VARCHAR(MAX),
    StartTime DATETIME,
    EndTime DATETIME
)

INSERT INTO @tCalendar(RequestedFor,MeetingType,RoomName,StartTime,EndTime)
SELECT req as requestedfor
      ,meet as meetingtype
      ,room as rooms
      ,start as starttime
      ,end as endtime

      --,u.datetime2 as endtime
    FROM mytable



        DECLARE @tCalendarHours TABLE
    (
        RequestedFor VARCHAR(50),
        MeetingType VARCHAR(50),
        RoomName VARCHAR(50),
        Hour INT
        )

    DECLARE @StartHour INT,@EndHour INT, @StartTime DATETIME, @EndTime DATETIME
    WHILE ((SELECT COUNT(*) FROM @tCalendar) > 0)   
    BEGIN
        SET @StartTime = (SELECT TOP 1 StartTime FROM @tCalendar)
        SET @EndTime = (SELECT TOP 1 EndTime FROM @tCalendar)
        SET @StartHour = (SELECT TOP 1 DATEPART(HOUR,DATEADD(HOUR,0,StartTime)) FROM @tCalendar)
        SET @EndHour = (SELECT TOP 1 DATEPART(HOUR,DATEADD(HOUR,0,EndTime)) FROM @tCalendar)
        WHILE @StartHour <= @EndHour
        BEGIN
            INSERT INTO @tCalendarHours
            SELECT RequestedFor,MeetingType,RoomName,@StartHour FROM @tCalendar WHERE StartTime = @StartTime AND EndTime = @EndTime
            SET @StartHour = @StartHour + 1
        END
        DELETE @tCalendar WHERE StartTime = @StartTime AND EndTime = @EndTime
    END