cursor not operating as expected

Question

Post reply

cursor not operating as expected

winston Smith

SSCoach

Points: 19489
More actions
January 26, 2007 at 2:50 am

#115919

hi. i have a cursor. it gets 3 rows from a table. I am 100% sure there are only 3 rows in the #Files table, as i insert them myself just before the cursor starts. it has a while @@fetch_status = 0 begin... line. so i thought it should go around 3 times, once on each of the rows and stop, but no, it does itterate through each of the 3 rows, but then tries to go around a 4th time, which causes an error. here is my cursor code, with all unnecessary extra code removed, and it still doesnt work. can you see anything wrong?

DECLARE

CheckFilesCursor CURSOR

FOR

SELECT files FROM #Files

OPEN CheckFilesCursor

FETCH next FROM CheckFilesCursor INTO @Filename

WHILE

@@fetch_status = 0

BEGIN

select convert(varchar(3),@count)+ ' '+ + @filename

set

@count = @count + 1

FETCH next FROM CheckFilesCursor INTO @Filename

END

CLOSE CheckFilesCursor

DEALLOCATE CheckFilesCursor

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 1

Hi,

What is the error message that you receive from this? I have tried using the cursor with the following and have not managed to recreate the problem...

DECLARE @count INT

DECLARE @filename VARCHAR(10)

SET @count = 0

CREATE TABLE #files(

files VARCHAR(10)

&nbsp

INSERT INTO #files VALUES ('file001')

INSERT INTO #files VALUES ('file002')

INSERT INTO #files VALUES ('file003')

DECLARE CheckFilesCursor CURSOR

FOR

SELECT files FROM #Files

OPEN CheckFilesCursor

FETCH next FROM CheckFilesCursor INTO @Filename

WHILE @@fetch_status = 0

BEGIN

select convert(varchar(3),@count)+ ' '+ + @filename

set @count = @count + 1

FETCH next FROM CheckFilesCursor INTO @Filename END CLOSE CheckFilesCursor DEALLOCATE CheckFilesCursor

DROP TABLE #files

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

winston Smith SSCoach Points: 19489 More actions · Answer 2

i just saw what was wrong. below the cursor code i had two functions, and as soon as the cursor finished, it just went straight onto the functions, that provided the errors. once i put a goto SucessEnd, and a function called SuccessEnd: it did not go near the two functions. blindingly obvious now that i look at it. cant wait to get to sql 2005 and use try{} catch{}. this issue would never have happened. Thanks for looking at my problem!

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Of course, if you used any one of a thousand or so set based solutions for this problem, you wouldn't have to worry about what a cursor does or doesn't do... here are two different methods... both will beat the pants off a cursor or a while loop performance wise and resource wise...

CREATE TABLE #Files

(Files VARCHAR(10))

INSERT INTO #Files VALUES ('file001')

INSERT INTO #Files VALUES ('file002')

INSERT INTO #Files VALUES ('file003')

--===== Method 1

SELECT CAST(CAST(RIGHT(Files,3) AS INT) AS VARCHAR(3)) + ' '+ Files

FROM #Files

ORDER BY Files

--===== Method 2

SELECT IDENTITY(INT,1,1) AS RowNum,

Files

INTO #MyHead

FROM #Files

ORDER BY Files

SELECT CAST(RowNum AS VARCHAR(10)) + ' ' + Files

FROM #MyHead

ORDER BY Files -- or Rownum if you prefer

DROP TABLE #Files

DROP TABLE #MyHead

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions