Cursor working on first run only

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 2008 Forums

Transact-SQL (2008)

Cursor working on first run only

Author

Topic

grimmus
Yak Posting Veteran

53 Posts

Posted - 2011-11-16 : 10:35:09

Hi,

I am doing a test using a cursor below but it only seems to output the results the first time I execute. Every other time it just says 'commands completed successfully'.

I thought closing and deallocating the cursor might help but it's still the same. Thanks for any tips.

DECLARE @name VARCHAR(50)

DECLARE user_cursor CURSOR FOR
SELECT DISTINCT UserCode FROM report
WHERE UserCode <> 0

OPEN user_cursor

WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM user_cursor into @name
print @name
END

CLOSE user_cursor
DEALLOCATE user_cursor

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-11-16 : 10:42:52

missing FETCH


DECLARE @name VARCHAR(50)

DECLARE user_cursor CURSOR FOR
SELECT DISTINCT UserCode FROM report
WHERE UserCode <> 0

OPEN user_cursor 

FETCH NEXT FROM user_cursor into @name

WHILE @@FETCH_STATUS = 0
  BEGIN
  FETCH NEXT FROM user_cursor into @name
  print @name
END

CLOSE user_cursor 
DEALLOCATE user_cursor

But don't use cursors. they are really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really, really

slow

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

grimmus
Yak Posting Veteran

53 Posts

Posted - 2011-11-16 : 10:56:56

Thank you very much.

Yes, i've decided to use a table variable instead because it took 3 minutes to insert 12000 rows into a table !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources