循环记录集并使用结果执行另一个 SQL 选择并返回结果

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

提问by J K

I am completely new to stored procedure. This time, I need to create a stored procedure in MS SQL.

我对存储过程完全陌生。这一次，我需要在 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.

如何传入 SomeID。使用此 SomeID 从表 ListOfProducts 中选择记录集。然后循环遍历这个记录集。

Let's say I pass in SomeID = 35.

假设我传入 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.

因此，记录集将返回 2 条 SomeID 为 35 的记录。在循环中，我将获得 ProductID 8 和 11，这将用于从另一个表中进行另一个选择。

The stored procedure should return the results from the 2nd select.

存储过程应该返回第二次选择的结果。

How can I do this in MS SQL stored procedure?

如何在 MS SQL 存储过程中执行此操作？

Sorry, for this newbie question. Thanks for any help.

对不起，对于这个新手问题。谢谢你的帮助。

回答by Ken Clark

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

回答by digscoop

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.

如果您不需要对无法在整个集合上完成的产品执行某些操作，那么编写显式循环可能没有意义。SQL Server 可以自己更好地处理这样的事情。我不知道你的桌子是什么样的，但你应该尝试一些看起来更像这样的东西。

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

回答by micabug

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

回答by parkerw262

Do something like this:

做这样的事情：

Declare @ID int

SET @ID = 35

SELECT
   p.SomeID
  ,p.ProductID
 FROM ListOfProducts p
  WHERE p.SomeID = @ID
 -----------------------
   --Or if you have to join to get it
  Declare @ID int

  SET @ID = 35

    SELECT
      c.SomeID
      ,p.ProductID
      ,p.ProductName

    FROM ListOfProducts p
    INNER JOIN categories c on p.ProductID = c.SomeID
    WHERE p.SomeID = @ID

回答by Aleksandr Fedorenko

You can use option with WHILEloop and BREAK/CONTINUEkeywords

您可以将选项与WHILE循环和BREAK/CONTINUE关键字一起使用

CREATE PROC dbo.yourProcName
@SomeID int
AS
BEGIN
  IF OBJECT_ID('tempdb.dbo.#resultTable') IS NOT NULL DROP TABLE dbo.#resultTable
  CREATE TABLE dbo.#resultTable
   (Col1 int, Col2 int)  
  DECLARE @ProductID int = 0
  WHILE(1=1)
    BEGIN
      SELECT @ProductID = MIN(ProductID) 
      FROM ListOfProducts 
      WHERE SomeID = @SomeID AND ProductID > @ProductID

      IF @ProductID IS NULL
      BREAK
      ELSE

      INSERT dbo.#resultTable
      SELECT Col1, Col2
      FROM dbo.yourSearchTable
      WHERE ProductID = @ProductID

      CONTINUE      
    END
  SELECT *
  FROM dbo.#resultTable 
END    

Demo on SQLFiddle

SQLFiddle 上的演示