Friday, April 24, 2015

@@FETCH_STATUS in nested loops

Does fetch status in nested loops conflict?

I have a script that should output a cluster record line and for each cluster record it must create a line for each household in the cluster.  All the data is pulled from one table, 'LFS_APRIL_2015.dbo.LISTING'.

This is the script:


--VARIABLE DECLARATION DECLARE @CLUSTER FLOAT, @HOUSEHOLD_NUMBER FLOAT, @FULL_ADDRESS CHAR(50), @HEAD_NAME CHAR(24)
--CLUSTER LOOP
DECLARE CLUSTER_CURSOR CURSOR FOR SELECT [LFS_APRIL_2015].[dbo].[LISTING].CLUSTER from [LFS_APRIL_2015].[dbo].[LISTING] where CLUSTER IS NOT NULL and DISTRICT = 1 OPEN CLUSTER_CURSOR FETCH NEXT FROM CLUSTER_CURSOR INTO @CLUSTER WHILE @@FETCH_STATUS = 0 BEGIN
--OUTPUT THE CLUSTER RECORD PRINT STR(@CLUSTER,3) + STR(1,1) + STR(24,3) --HOUSEHOLD LOOP DECLARE HOUSEHOLD_CURSOR CURSOR FOR SELECT [LFS_APRIL_2015].[dbo].[LISTING].HOUSEHOLD_NUMBER, FULL_ADDRESS, HEAD__INSTITUTION__BUSINESS_NAME from [LFS_APRIL_2015].[dbo].[LISTING] where CLUSTER IS NOT NULL AND IS_HOUSEHOLD = 1 AND CLUSTER = @CLUSTER OPEN HOUSEHOLD_CURSOR FETCH NEXT FROM HOUSEHOLD_CURSOR INTO @HOUSEHOLD_NUMBER, @FULL_ADDRESS, @HEAD_NAME WHILE @@FETCH_STATUS = 0 BEGIN --OUTPUT THE HOUSEHOLDS IN THE CLUSTER PRINT right('000'+cast(@CLUSTER as varchar(10)),3) + STR(2,1) + STR(@HOUSEHOLD_NUMBER,2) + @FULL_ADDRESS + @HEAD_NAME + '000' + ' ' + STR(0,1) FETCH NEXT FROM HOUSEHOLD_CURSOR INTO @HOUSEHOLD_NUMBER, @FULL_ADDRESS, @HEAD_NAME END CLOSE HOUSEHOLD_CURSOR DEALLOCATE HOUSEHOLD_CURSOR FETCH NEXT FROM CLUSTER_CURSOR INTO @CLUSTER END CLOSE CLUSTER_CURSOR DEALLOCATE CLUSTER_CURSOR

It appears however that the clusters are being repeated.

Where have I gone wrong!

No comments:

Post a Comment