Hello, I need help transforming a current script into on that can loop through some data for me. As you can see I currently have the ItemName and Address commented out "-- Set @ItemName = '95B046125' Set @Address = '195 WELLESLEY ST E'" and then I have to uncomment it and recomment it and so on. Is there a quick way where I can just have the query loop through all of the code highlighted in red.
use portal_rehrigtoronto_CrossDock_Prod
go
BEGIN TRY
BEGIN TRAN
DECLARE @ItemName varchar(10)
DECLARE @Address varchar(100)
DECLARE @ItemID int
DECLARE @ToLocationID int
DECLARE @ToStatusID int
DECLARE @ToStopID int
-- Set @ItemName = '95B046125' Set @Address = '195 WELLESLEY ST E'
-- Set @ItemName = '95B046122' Set @Address = '197 WELLESLEY ST E'
Set @ItemName = '95B045659' Set @Address = '387 SHERBOURNE ST'
-- Set @ItemName = '95B048217' Set @Address = '30 COVINGTON RD'
-- Set @ItemName = '95B044158' Set @Address = '993 OCONNOR DR'
-- Set @ItemName = '95B046063' Set @Address = '995 OCONNOR DR'
-- Set @ItemName = '95B046047' Set @Address = '8 MALLORY GDNS'
-- Set @ItemName = '95B045912' Set @Address = '1157 OCONNOR DR'
-- Set @ItemName = '95B045902' Set @Address = '28 SENTINEL RD'
-- Set @ItemName = '95B045901' Set @Address = '20 BROOKWELL DR'
-- Set @ItemName = '95B044899' Set @Address = '22 BROOKWELL DR'
-- Set @ItemName = '95B046066' Set @Address = '4140 BATHURST ST'
-- Set @ItemName = '95B046065' Set @Address = '142 WELLESLEY St E'
-- Set @ItemName = '95B046064' Set @Address = '433 SILVERSTONE DR'
-- Set @ItemName = '95G030658' Set @Address = '435 SILVERSTONE DR'
-- Set @ItemName = '95B046069' Set @Address = '437 SILVERSTONE DR'
-- Set @ItemName = '95B046068' Set @Address = '262 SHERBOURNE ST'
-- Set @ItemName = '95B046067' Set @Address = '8 Humber Blvd'
SET @ToStatusID = 5
--In service = 5, Out of Service = 8
PRINT '-----------------------------------------------------------------'
PRINT 'Get the ItemID'
SELECT @ItemID = ItemID FROM LAItem WHERE ViItemName = @ItemName
PRINT 'ItemID = ' + cast(@ItemID as varchar)
-- IF Location ID = -2 or -3 then you need to manually set the ToStopID and not use the query below
PRINT '-----------------------------------------------------------------'
PRINT 'Get the LocationID'
IF @Address = 'Kendrew'
BEGIN
SET @ToLocationID = -2
END
ELSE IF @Address = 'CrossDock'
BEGIN
SET @ToLocationID = -5
END
ELSE
BEGIN
SELECT @ToLocationID = LocationID FROM LALocation WHERE Address1 = @Address
END
PRINT 'LocationID = ' + cast(@ToLocationID as varchar)
IF @ToLocationID IS NOT NULL
BEGIN
PRINT '-----------------------------------------------------------------'
PRINT 'Get the StopID for the ToLocationID'
SELECT @ToStopID = StopID FROM LAStop WHERE LocationID = @ToLocationID
PRINT 'StopID = ' + cast(@ToStopID as varchar)
PRINT '-----------------------------------------------------------------'
PRINT 'Add the TransHeader Record'
DECLARE @NewLATransHeader int
DECLARE @LastKeyField int
SELECT @LastKeyField=min(KeyField) FROM LATransHeader
if @LastKeyField = 1 SET @LastKeyField = 0
INSERT INTO LATransHeader (TransSourceID, KeyField, TransDate)
VALUES (-1,@LastKeyField-1,'2010-07-30')--GETDATE())
SET @NewLATransHeader = SCOPE_IDENTITY()
PRINT 'NewLATransHeader = ' + CAST(@NewLATransHeader as varchar)
PRINT '-----------------------------------------------------------------'
PRINT 'Find Where the Item is at'
DECLARE @FromStatusID int
DECLARE @FromStopID int
DECLARE @Quantity int
SELECT @FromStopID=StopID, @FromStatusID=StatusID,@Quantity= Quantity
FROM LAInventory
WHERE (ItemID = @ItemID) AND (Quantity > 0)
PRINT 'FromStatusID = ' + CAST(@FromStatusID as varchar)
PRINT 'FromStopID = ' + CAST(@FromStopID as varchar)
PRINT 'Quantity = ' + CAST(@Quantity as varchar)
PRINT '------------------------------------------------------------------'
PRINT 'Debit where it is at'
UPDATE LAInventory
SET Quantity = Quantity - @Quantity
FROM LAInventory
WHERE (StopID = @FromStopID) AND (StatusID = @FromStatusID) AND (ItemID = @ItemID)
PRINT '------------------------------------------------------------------'
PRINT 'CREDIT the To Stop'
if not exists (SELECT * FROM LAInventory WHERE (StopID = @ToStopID) AND (StatusID = @ToStatusID) AND (ItemID = @ItemID))
INSERT INTO LAInventory (StopID, StatusID, ItemID, Quantity)
VALUES (@ToStopID,@ToStatusID,@ItemID,0)
UPDATE LAInventory
SET Quantity = Quantity + @Quantity
FROM LAInventory
WHERE (StopID = @ToStopID) AND (StatusID = @ToStatusID) AND (ItemID = @ItemID)
PRINT '------------------------------------------------------------------'
PRINT 'Add The Trans Detail'
INSERT INTO LATransDetail (ItemID, TransHeaderID, StopID, StatusID, Quantity)
VALUES (@ItemID, @NewLATransHeader, @ToStopID,@ToStatusID,@Quantity)
INSERT INTO LATransDetail (ItemID, TransHeaderID, StopID, StatusID, Quantity)
VALUES (@ItemID, @NewLATransHeader, @FromStopID, @FromStatusID, @Quantity * -1)
END
ELSE
BEGIN
PRINT '------------------------------------------------------------------'
PRINT 'LocationID was null, move aborted'
END
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRAN
PRINT ''
PRINT '-----------------------------------------------------------------'
EXEC usp_laRethrowError
PRINT 'The Transaction was ROLLBACKED'
END CATCH