Cursor fetch stuck on 1st row. 3

[emccormick]

SQL2005, W2K3-SP2

I can't see why the cursor in this stored procedure won't move past the 1st row. The select statement hits a table with ~5k unique AuthorizationNumber(s).

The while loop works because the @Count increments, but the other vars just sit on the first row.

------

ALTER procedure [glopp].[meetcomp_parse]
as begin

declare @AuthorizationNumber varchar(100);
declare @Competitor1 varchar(100);
declare @CompmodelNumber1 varchar(100);
declare @CompEUPrice1 varchar(100);
declare @CompPPrice1 varchar(100);
declare @CompDstPrice_1 varchar(100);
declare @CompResPrice1 varchar(100);
declare @Competitor2 varchar(100);
declare @CompmodelNumber2 varchar(100);
declare @CompEUPrice2 varchar(100);
declare @CompPPrice2 varchar(100);
declare @CompDstPrice_2 varchar(100);
declare @CompResPrice2 varchar(100);
declare @Competitor3 varchar(100);
declare @CompmodelNumber3 varchar(100);
declare @CompEUPrice3 varchar(100);
declare @CompPPrice3 varchar(100);
declare @CompDstPrice_3 varchar(100);
declare @CompResPrice3 varchar(100);
declare @Competitor4 varchar(100);
declare @CompmodelNumber4 varchar(100);
declare @CompEUPrice4 varchar(100);
declare @CompPPrice4 varchar(100);
declare @CompDstPrice_4 varchar(100);
declare @CompResPrice4 varchar(100);
declare @Competitor5 varchar(100);
declare @CompmodelNumber5 varchar(100);
declare @CompEUPrice5 varchar(100);
declare @CompPPrice5 varchar(100);
declare @CompDstPrice_5 varchar(100);
declare @CompResPrice5 varchar(100);

declare @count int;

set @count = 0;

declare mycursor cursor for
select
distinct(AuthorizationNumber),
Competitor1,
CompmodelNumber1,
CompEUPrice1,
CompPPrice1,
CompDstPrice_1,
CompResPrice1,
Competitor2,
CompmodelNumber2,
CompEUPrice2,
CompPPrice2,
CompDstPrice_2,
CompResPrice2,
Competitor3,
CompmodelNumber3,
CompEUPrice3,
CompPPrice3,
CompDstPrice_3,
CompResPrice3,
Competitor4,
CompmodelNumber4,
CompEUPrice4,
CompPPrice4,
CompDstPrice_4,
CompResPrice4,
Competitor5,
CompmodelNumber5,
CompEUPrice5,
CompPPrice5,
CompDstPrice_5,
CompResPrice5
from meetcomp;

open mycursor

fetch next from mycursor into
@AuthorizationNumber,
@Competitor1,
@CompmodelNumber1,
@CompEUPrice1,
@CompPPrice1,
@CompDstPrice_1,
@CompResPrice1,
@Competitor2,
@CompmodelNumber2,
@CompEUPrice2,
@CompPPrice2,
@CompDstPrice_2,
@CompResPrice2,
@Competitor3,
@CompmodelNumber3,
@CompEUPrice3,
@CompPPrice3,
@CompDstPrice_3,
@CompResPrice3,
@Competitor4,
@CompmodelNumber4,
@CompEUPrice4,
@CompPPrice4,
@CompDstPrice_4,
@CompResPrice4,
@Competitor5,
@CompmodelNumber5,
@CompEUPrice5,
@CompPPrice5,
@CompDstPrice_5,
@CompResPrice5

while @@fetch_status = 0
begin

print @@fetch_status;
print @count;
print @AuthorizationNumber;
print @Competitor1;
print @Competitor2;
print @Competitor3;
print @Competitor4;
print @Competitor5;

set @count = @count + 1;


end;

close mycursor;
deallocate mycursor;

end;


--------

Yes, the table structure leaves plenty to be desired and most would say making cursors this big is bad as well.

Can anyone see any syntax problems?

Is there a limit to the number of vars a cursor can hold?

Thanks!
e

 

[ESquared]

We would say that making cursors of any kind is bad, however small or large they are. Honestly, can't you avoid using a cursor entirely? Why do you need it? The code you posted is a nightmare.

The first syntax problem I see is that the fetch_status is more complicated than that. I almost never work with cursors so I have no clue what the correct structure is, but if you use one of the templates provided to you with your SQL client install, you'll see the correct structure.

Sigh. Fine. I'll get it and show you myself in case you don't know how to do that:

-- =============================================
-- Declare and using a READ_ONLY cursor
-- =============================================
DECLARE <cursor_name, sysname, test_cursor> CURSOR
READ_ONLY
FOR <select_statement, , SELECT au_fname FROM pubs.dbo.authors>

DECLARE @name varchar(40)
OPEN <cursor_name, sysname, test_cursor>

FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
--		PRINT 'add user defined code here'
--		eg.
		DECLARE @message varchar(100)
		SELECT @message = 'my name is: ' + @name
		PRINT @message
	END
	FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO @name
END

CLOSE <cursor_name, sysname, test_cursor>
DEALLOCATE <cursor_name, sysname, test_cursor>

In Query Analyzer, click the first dropdown on the toolbar, select "Template..." and open the "Using Cursor" directory. In Management Studio, I dunno.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]

 

[SQLSister]

You are writing cursors becasue you find them amusing? Whatever you plan to do (which isn't apparent from what you showed us here) can probably be done in a set-based fashion much faster. There isa reason why the people who are experienced in T-SQl code avoid them. If you want to become experienced, I suggest you avoid them as well and learn to write set-based code/

However, if you should need to write a cursor (which is an extremely bad practice 90+% of the time), I would suggest that you look up the actual syntax for a cursor. You have never fetched the next row.

"NOTHING is more important in a database than integrity." ESquared

 

[pjw001]

Just in case you didn't spot the clue to solving your problem, you need to have a fetch within the loop.

Just after the "set set @count = @count+1" put another fetch just like the first one.

 

[emccormick]

Thanks all for the wisdom of your experience.

Would it help if I said the sheer volume of variables (and this is only a third of the columns involved!) is why I missed the in-loop fetch???

ESquared: I'd never opened the template explorer before. Thanks!

SQLSister: I think I can see a cursorless approach.

pjw001: Thanks for the simple answer.

Warm Regards,
e

 

[ESquared]

It's also available from Edit... Insert Template...

and also, play with the option Edit... Replace Template Parameters...