TSQL - While代码中的While

Question

有没有人能告诉我为什么这不管用。我想在一个循环内循环.....

BEGIN
SET NOCOUNT ON;

Declare @TempLocations Table (PK int Identity(1,1) not null Primary key, LocationID Int)
Declare @TempItems Table (PK1 int Identity(1,1) not null Primary key, ItemID int)

Declare @TempTable Table (ID Int Identity(1,1), LocationID int, ItemID int)

Declare @MaxLocationID int,
  @MaxItemID Int,
  @LocationID int,
  @ItemID int

-- Load "Can be sold from" Locations into Temp Table  
  Insert Into @TempLocations (LocationID)
  Select LocationID from WMS.Locations
  Where CanBeSoldFrom = 'Checked'

  Set @MaxItemID = (Select MAX(PK1) From @TempItems)
  Set @LocationID = 1

-- Load "IsActive" Items into Temp Table
  Insert Into @TempItems (ItemID)
  Select ItemID from IMS.ItemDetails
  Where IsActive = 'Checked'

  Set @MaxLocationID = (Select MAX(PK) From @TempLocations)
  Set @ItemID = 1

--Main Code
  While @LocationID <= @MaxLocationID
   Begin

      While @ItemID <= @MaxItemID
      Begin
      Insert into @TempTable (LocationID, ItemID)
      Values (@LocationID, @ItemID)

      Set @ItemID = @ItemID + 1
      end

   Set @LocationID = @LocationID + 1
  End

Select * from @TempTable
END

我尝试得到的结果是这样的

@tempTable =

LocationID =1 ItemID =1 ItemID =2 ItemID =3 ItemID =4

LocationID =2 ItemID =1 ItemID =2 ItemID =3 ItemID =4

以此类推......

Answer 1

这根本不应该在过程代码中完成。使用纯SQL，让DB引擎来做它的工作，它会执行得更好，更少的代码=更少的错误。我不确定我是否完全理解您想要的结果，但我认为这就够了：

​

select 
    LocationID,
    ItemID 
from
(
    Select LocationID from WMS.Locations
    Where CanBeSoldFrom = 'Checked'
)
cross join
(
    Select ItemID from IMS.ItemDetails
    Where IsActive = 'Checked'
)
order by
    LocationID,
    ItemID

​

Answer 2

您的查询在@TempItems中填充任何内容之前选择@MaxItemID。因此，@MaxItemID为null。您必须切换语句Set @MaxLocationID = (Select MAX(PK) From @TempLocations)和Set @MaxItemID = (Select MAX(PK1) From @TempItems)。

我同意Jeremy的观点，但是使用基于集合的编程会更好。