0

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
2
Contributors
3
Replies
4
Views
6 Years
Discussion Span
Last Post by buddylee17
0

Use a table variable and loop through it like an array:

DECLARE @ItemName VARCHAR(10)
DECLARE @Address VARCHAR(100)
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
		
		--Delete the row from the table variable
		DELETE @Items WHERE @ItemName = ItemName AND @Address = Address
	END

Edited by buddylee17: n/a

0

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
0

No, your Begin/End nesting is wrong. The Delete @Items statement should be the last line in the While loop (before END).

The DELETE statement has 2 purposes:
1. Remove the row after you are done with it.
2. SET THE WHILE condition to FALSE once all rows are deleted and stop the loop.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.