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

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

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

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.