Im sorry but I am new to this.....but is this how the entire script should look????
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
DECLARE @Items TABLE (ItemName VARCHAR(10), Address VARCHAR(100))
INSERT @Items
SELECT '95B046047' , '8 MALLORY GDNS'
UNION ALL
SELECT '95B045912' , '1157 OCONNOR DR'
UNION ALL
SELECT '95B045902' , '28 SENTINEL RD'
UNION ALL
SELECT '95B045901' , '20 BROOKWELL DR'
UNION ALL
SELECT '95B044899' , '22 BROOKWELL DR'
UNION ALL
SELECT '95B046066' , '4140 BATHURST ST'
UNION ALL
SELECT '95B046065' , '142 WELLESLEY St E'
UNION ALL
SELECT '95B046064' , '433 SILVERSTONE DR'
UNION ALL
SELECT '95G030658' , '435 SILVERSTONE DR'
UNION ALL
SELECT '95B046069' , '437 SILVERSTONE DR'
UNION ALL
SELECT '95B046068' , '262 SHERBOURNE ST'
UNION ALL
SELECT '95B046067' , '8 Humber Blvd'
--Loop through the table variable, treating it like an array
WHILE (SELECT COUNT(*) FROM @Items) > 0
BEGIN
--Grab first row from table and assign values to local variables
SELECT TOP 1 @ItemName = ItemName, @Address = Address FROM @Items
PRINT @ItemName + N' ' + @Address
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
--Delete the row from the table variable
DELETE @Items WHERE @ItemName = ItemName AND @Address = Address
END
