Loop within a loop - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Loop within a loop

Author

Topic

mmckimson
Starting Member

6 Posts

Posted - 2013-08-03 : 16:23:43

Background:

I am creating a routine that is automating the creation of invoices that inserts the data from one of our SQL Server tables to a table in a linked server. Due to the limitation of the ODBC driver / underlying application used to create the linked server, I can't simply INSERT a SET of data but must instead create the invoices customer by customer and iterate through the records for each customer.

Code:

Hence, what I've done is create a test routine that first creates a loop that determines the customer, and within that loop, runs another loop that processes the data for that customer and writes something to the console for each customer transaction. The relevant code is as follows:


SET NOCOUNT ON

Declare @i int --iterator to determine number of customer records to be processed
Declare @iCount int --rowcount for number of customer records to be processed
Declare @QB_ID varchar(100) --value to determine number of customers to be processed
Declare @i2 int -- iterator for detail transactions
Declare @iCount2 int --rowcount for detail transactions
Declare @QB_ID2 varchar(100) --the customer to be processed
Declare @TransQty varchar(100) -- quantity
Declare @ItemID varchar(100) -- QB Item ID

SET @i = 1 --initialize
CREATE TABLE ID_COUNT(ID int identity(1,1), QB_ID varchar(100))
INSERT INTO ID_COUNT(QB_ID) SELECT DISTINCT(QB_ID) FROM QB_INVOICELINES WHERE QB_ID IS NOT NULL

SET @iCount = @@ROWCOUNT --SCOPE_IDENTITY() would also work
CREATE CLUSTERED INDEX IDX_TMP on ID_COUNT(ID) WITH FILLFACTOR = 100

WHILE @i <= @iCount
BEGIN
SELECT @QB_ID = QB_ID FROM ID_COUNT WHERE ID = @i
--begin sub routine
	SET @i2 = 1
	CREATE TABLE TRANS_COUNT(TRANS_ID int identity(1,1), QB_TRANS_ID varchar(100))
	INSERT INTO TRANS_COUNT(QB_TRANS_ID)SELECT QB_ID FROM QB_INVOICELINES WHERE QB_ID = @QB_ID
	SET @iCount2 = @@ROWCOUNT
	CREATE CLUSTERED INDEX IDX_TMP on TRANS_COUNT(TRANS_ID) WITH FILLFACTOR = 100
	WHILE @i2 <= @iCount2
	BEGIN
	SELECT @QB_ID2 = QB_TRANS_ID FROM TRANS_COUNT WHERE TRANS_ID = @i2 
	Select @TransQty = Qty, @ItemID = QB_ITEM_ID from QB_INVOICELINES WHERE QB_ID = @QB_ID2
	PRINT 'Transaction quantity of ' + @TransQty +  ' for item ID ' + @ItemID + ' for customer ID ' + @QB_ID2 + ' is now posted'
	SET @i2 = @i2 + 1
	END	
	DROP TABLE TRANS_COUNT
--end sub routine	
SET @i = @i + 1
END
DROP TABLE ID_COUNT

Issue:

While this routine seemingly runs correctly and does recognize the appropriate number of transactions for each of the 3 customers in the underlying table, the "inner" loop seems to repeat the last transaction of the series for each customer X number of times, with X being the correct number of customer transactions, as opposed to having each transaction.

The following images display the data from the table I am using to test as well as the console messages returned:

Data:

Console message:

I've looked at this until I'm blue in the face, and can't figure out what is wrong. Any help would be appreciated!

Cheers!
Mike

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-03 : 17:07:08

[code]~~INSERT INTO TRANS_COUNT(QB_TRANS_ID)SELECT QB_ID FROM QB_INVOICELINES WHERE QB_ID = @QB_ID~~
INSERT INTO TRANS_COUNT(QB_TRANS_ID)SELECT QB_TRANS_ID FROM QB_INVOICELINES WHERE QB_ID = @QB_ID[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-03 : 17:15:02

Wouldn't this be easier?

SET NOCOUNT ON

DECLARE	curOutput CURSOR FOR	SELECT		QTY,
						QB_ITEM_ID,
						QB_TRANS_ID
				FROM		dbo.QB_INVOICELINES
				WHERE		QB_ID IS NOT NULL
				ORDER BY	QB_TRANS_ID,
						QB_ITEM_ID;

DECLARE	@Quantity INT,
	@ItemID VARCHAR(100),
	@CustomerID VARCHAR(100);

OPEN	curOutput

FETCH	NEXT
FROM	curOutput
INTO	@Quantity,
	@ItemID,
	@CustomerID;

WHILE @@FETCH_STATUS = 0
	BEGIN
		PRINT	'Transaction quantity of ' + CAST(@Quantity AS VARCHAR(12)) +  ' for item ID ' + @ItemID + ' for customer ID ' + @CustomerID + ' is now posted.'

		FETCH	NEXT
		FROM	curOutput
		INTO	@Quantity,
			@ItemID,
			@CustomerID;
	END

CLOSE		curOutput
DEALLOCATE	curOutput

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-03 : 17:20:02

Or, if you insist in keeping your loops, there is no need to recreate TRANS_COUNT table over and over again.

Declare @i int --iterator to determine number of customer records to be processed
Declare @iCount int --rowcount for number of customer records to be processed
Declare @QB_ID varchar(100) --value to determine number of customers to be processed
Declare @i2 int -- iterator for detail transactions
Declare @iCount2 int --rowcount for detail transactions
Declare @QB_ID2 varchar(100) --the customer to be processed
Declare @TransQty varchar(100) -- quantity
Declare @ItemID varchar(100) -- QB Item ID

SET @i = 1 --initialize

CREATE TABLE	#ID_COUNT
		(
			ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
			QB_ID VARCHAR(100) NOT NULL
		);

INSERT		#ID_COUNT
		(
			QB_ID
		)
SELECT DISTINCT	QB_ID
FROM		dbo.QB_INVOICELINES
WHERE		QB_ID IS NOT NULL;

SET	@iCount = @@ROWCOUNT;

CREATE TABLE	#TRANS_COUNT
		(
			TRANS_ID INT IDENTITY(1, 1),
			QB_TRANS_ID VARCHAR(100) NOT NULL
		);

WHILE @i <= @iCount
	BEGIN
		SELECT	@QB_ID = QB_ID
		FROM	#ID_COUNT
		WHERE	ID = @i;

		--begin sub routine
		SET	@i2 = 1;
	
		TRUNCATE TABLE	#TRANS_COUNT;

		INSERT #TRANS_COUNT
			(
				QB_TRANS_ID
			)
		SELECT	QB_TRANS_ID
		FROM	dbo.QB_INVOICELINES
		WHERE	QB_ID = @QB_ID;
	
		SET @iCount2 = @@ROWCOUNT;

		WHILE @i2 <= @iCount2
			BEGIN
				SELECT	@QB_ID2 = QB_TRANS_ID
				FROM	#TRANS_COUNT
				WHERE	TRANS_ID = @i2;
	
				SELECT	@TransQty = Qty,
					@ItemID = QB_ITEM_ID
				FROM	dbo.QB_INVOICELINES
				WHERE	QB_ID = @QB_ID2;
	
				PRINT	'Transaction quantity of ' + @TransQty +  ' for item ID ' + @ItemID + ' for customer ID ' + @QB_ID2 + ' is now posted.'
	
				SET @i2 = @i2 + 1
			END	

		--end sub routine	
		SET @i = @i + 1
	END

DROP TABLE	#ID_COUNT,
		#TRANS_COUNT;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

mmckimson
Starting Member

6 Posts

Posted - 2013-08-03 : 18:47:11

Indeed... after modifying to order the cursor output by QB_ID (how the invoice is generated, it worked perfectly.

Thanks for your help!
Mike

quote:

Originally posted by SwePeso

Wouldn't this be easier?

SET NOCOUNT ON

DECLARE	curOutput CURSOR FOR	SELECT		QTY,
						QB_ITEM_ID,
						QB_TRANS_ID
				FROM		dbo.QB_INVOICELINES
				WHERE		QB_ID IS NOT NULL
				ORDER BY	QB_TRANS_ID,
						QB_ITEM_ID;

DECLARE	@Quantity INT,
	@ItemID VARCHAR(100),
	@CustomerID VARCHAR(100);

OPEN	curOutput

FETCH	NEXT
FROM	curOutput
INTO	@Quantity,
	@ItemID,
	@CustomerID;

WHILE @@FETCH_STATUS = 0
	BEGIN
		PRINT	'Transaction quantity of ' + CAST(@Quantity AS VARCHAR(12)) +  ' for item ID ' + @ItemID + ' for customer ID ' + @CustomerID + ' is now posted.'

		FETCH	NEXT
		FROM	curOutput
		INTO	@Quantity,
			@ItemID,
			@CustomerID;
	END

CLOSE		curOutput
DEALLOCATE	curOutput

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-03 : 19:16:50

You mean

ORDER BY QB_ID, InvoiceLine_ID

to have a deterministic order?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources