My name is Tom and I'm studying for my Data Platforms MCSE, its still early days so bear with me.
I'm creating my own practice database while I'm studying and have hit a snag because I, naturally, have been venturing into uncharted territories. For context, the database I'm creating is an Items database for a video game. What I'm trying to do is create a join table between Chests and Items and populate it.
Here is the code:
--Set Variables for randomizing Information, et al
DECLARE @localChestTier int, @ItemLimit int, @counter int,@ItemCounter int
SET @counter = 1 -- outter chest counter
SET @ItemCounter = 1 -- inner Items in each chest counter
WHILE @counter <= 1000 -- create 1000 chests full of items
SET @localChestTier = (SELECT Chests.ChestTier FROM Chests WHERE Chests.ChestID = @counter) -- Pulling item quality base line... TODO: this seems very rigid, examine better methods of implementation
SET @ItemLimit = (ABS(CHECKSUM(NEWID())) % (@localChestTier * 2)) + 1 -- double base tier for item cap
WHILE @ItemCounter <= @ItemLimit
INSERT ChestItems --Join Table
--ChestItemsID Primary Key Auto incremented
@counter,--ChestID from Chests
(SELECT TOP 1 Items.ItemID FROM Items WHERE Items.ItemTier = @localChestTier)--ItemID from Items
SET @ItemCounter +=1 -- next item
SET @counter += 1 -- next chest
The Query hangs for a minute plus before anything seems to happen, and I'm scratching my head as to why... Can anybody help?