cursor help - 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 2000 Forums

SQL Server Development (2000)

cursor help

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-09-13 : 11:19:33

Hi, all,

First a little disclaimer, I am not using the cursor to INSERT/UPDATE a db object, which should be done in SET way, instead, I need to manipulate a set of files individually.

Now, here is my code, and I have two things I need your help.

1. while my SELECT returns 1 row, why I got a duplicate at my Print @?

It seems the WHILE @@FETCH_STATUS part is not working as I have expected. No matter I used WHILE @@FETCH_STATUS = 0 or WHILE @@FETCH_STATUS <> -1, I always got both 0 myfilename, -1 myfilename printed.

2. Why I could not rerun the same code in a QA session to see the result? The 2nd time and thereafter only give me "The command(s) completed successfully." I thought DEALLOCATE part will free up the memory, and that should give me a clean restart. Am I wrong?

DECLARE Cursor_Files CURSOR for
(select statement)

DECLARE @FileName varchar(20)

OPEN Cursor_Files

WHILE @@FETCH_STATUS <> -1
BegIn
FETCH NEXT FROM Cursor_Files INTO @FileName
PRINT @@FETCH_STATUS
PRINT @FileName
--EXEC ('')
END

CLOSE Cursor_Files
DEALLOCATE Cursor_Files

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 11:21:23

[code]DECLARE @FileName varchar(20)

DECLARE Cursor_Files CURSOR for
(select statement)

OPEN Cursor_Files
FETCH NEXT FROM Cursor_Files INTO @FileName

WHILE @@FETCH_STATUS = 0
BegIn
PRINT @@FETCH_STATUS
PRINT @FileName
--EXEC ('')
FETCH NEXT FROM Cursor_Files INTO @FileName
END

CLOSE Cursor_Files
DEALLOCATE Cursor_Files[/code]

E 12°55'05.25"
N 56°04'39.16"

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-13 : 11:23:30

I always do a fetch before the while, then at the bottom of the loop

Post the sql, table ddl and sample data

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-09-13 : 11:24:35

On a second thought about above #1, I guess I could or should use a @counter = row count to loop through.

Does anyone know a good example of mixing the control of flow and cursor statements?

Thanks!

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-09-13 : 11:30:54

Ok, Peso's code worked, and yes fetch before the while is the way.

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources