TSQL - While in While code

Question

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 ......

It's hard to predict what's wrong... can you tell us what is the current outcome as is? Why isn't it working? — BeemerGuy
– BeemerGuy, Commented Dec 15, 2010 at 1:30

Jeremy Shimanek · Accepted Answer · 2010-12-15 05:05:54Z

1

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

answered Dec 15, 2010 at 5:05

Jeremy Shimanek

5123 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

willi_84 · Accepted Answer · 2010-12-15 07:31:23Z

0

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.

answered Dec 15, 2010 at 7:31

willi_84

1

Collectives™ on Stack Overflow

TSQL - While in While code

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related