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!

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

Please follow the basic Netiquette of all SQL forums. Post DDL that follows ISO-11179 rules for data element names. Use ISO-8601 formats for temporal data. Use induSTRy standard encodings (ISBN, UPC, GTIN, etc) and avoid needless dialect. Give clear specifications. Give sample data.

If you do not know that rows are not records, fields are not columns and tables are not files, then you should not be posting. SQL is a declarative language, so we hate cursors. We hate local variables, too. 

Using upper case letters is a code smell that says your mindset is still in punch cards and sequential processing. And finally you used a date in a data STRuctured name! Back in the 1960's, this is how we named magnetic tapes. In RDBMS, we name tables for the set of entities they model. 

Why are you using FLOAT? Is there really household number like 3.141592653 in your data model? But if it is really an integer, you still screwed up. What math do you do with it? 

The USPS standards use 5 lines of VARCHAR(35) for a Street address. But you pack all of that in CHAR(50). Think about how vague cluster is Size? Name? Location? WHAT?? 

The purpose of PRINT in T-SQL is debugging and not output. The purpose of SQL is to handle the data, not to format and display data, like you are doing. Learn what a presentation layer in RDBMS is. 

Follow the rules, and post that DDL, then we will try to help you. All you can get is a stinking kludge with this posting. 

It basically works, e.g.

DECLARE tables CURSOR FOR
	SELECT object_id, name FROM sys.tables;
DECLARE @object_id INT;
DECLARE @name SYSNAME;
DECLARE @NO_ERROR INT = 0;

OPEN tables;
FETCH NEXT FROM tables INTO @object_id, @name;
WHILE @@FETCH_STATUS = @NO_ERROR
BEGIN
	PRINT @name;

	DECLARE columns CURSOR FOR
		SELECT name FROM sys.columns WHERE object_id = @object_id;
	OPEN columns;
	WHILE @@FETCH_STATUS = @NO_ERROR
	BEGIN
		PRINT '    ' + @name;	
		FETCH NEXT FROM columns INTO @name;
	END;
	CLOSE columns;
	DEALLOCATE columns;

	FETCH NEXT FROM tables INTO @object_id, @name;
END;
CLOSE tables;
DEALLOCATE tables;

But there's offten no need for nesting it:

DECLARE both CURSOR FOR
	SELECT T.name, C.name FROM sys.tables T
		INNER JOIN sys.columns C ON C.object_id = T.object_id;;
DECLARE @table_name SYSNAME;
DECLARE @column_name SYSNAME;
DECLARE @last_name SYSNAME;
DECLARE @NO_ERROR INT = 0;

SET @last_name = '';
OPEN both;
FETCH NEXT FROM both INTO @table_name, @column_name;
WHILE @@FETCH_STATUS = @NO_ERROR
BEGIN
	IF ( @last_name != @table_name )
	BEGIN
		PRINT @table_name;
		PRINT '    ' + @column_name;
	END
	ELSE
	BEGIN
		PRINT '    ' + @column_name;
	END;

	SET @last_name = @table_name;
	FETCH NEXT FROM both INTO @table_name, @column_name;
END;
CLOSE both;
DEALLOCATE both;

So check your conditions in both cursor definitions.

The clusters repeat because, well, they repeat in the table. You could add DISTINCT to the first cursor to avoid this.

However, normally you return data with SELECT and not with PRINT, and in that case, all you need is:

SELECT cluster, household_number, full_address, head_institution_business_name
FROM  LFS_APRIL_2015.dbo.listing
WHERE cluster IS NOT NULL
  AND district = 1
  AND is_household
ORDER  BY cluster, household_number

!?? ####

Thanks Erland.

Select distinct in the first cursor did the trick.

Your point about using the output of the select statement instead using a print statement is well taken.  However, I am able to save the output from the messages tab in a .rpt file that suits my purpose of using the data in CSPRO (Census and Survey Processing Application from the U.S. Census bureau).

Once again thanks to you and to Stefan as well.  You actually helped as opposed to be as condescending as the other ####### that posted the first response!!!!

Desmond