CODEWITHSUNDEEP
sqlt-sql
JorgeI
JorgeI

Reputation: 11

TSQL - While in While code

Can someone please tell me why this wont work. I want to loop inside a loop ..... 

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

The result I am Tryinig to get is this

@tempTable =

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

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

and so on ......

Upvotes: 1

Views: 371

Answers (2)

willi_84
willi_84

Reputation: 1

Your query selects the @MaxItemID before anything is filled into @TempItems. Therefor @MaxItemID is null. You have to switch the Statements Set @MaxLocationID = (Select MAX(PK) From @TempLocations) and Set @MaxItemID = (Select MAX(PK1) From @TempItems).

I agree with Jeremy though, it would be better to do that with set-based-programming.

Upvotes: 0

Jeremy Shimanek
Jeremy Shimanek

Reputation: 512

This shouldn't be done in a procedural code at all. Use pure SQL and let the DB engine do it's job, it will perform much better, and less code = less bugs. I'm not sure I completely understand what results you want, but I think this does it:

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

Upvotes: 1

Related Questions