CURSOR - Is that a normal behaviour?

Question

CURSOR - Is that a normal behaviour?

Emil B

SSCertifiable

Points: 5568
More actions
September 10, 2015 at 11:13 am

#307606

Hi,
Today I was testing a piece of code like the one below, and my cursor got stuck reading record and inserting a new record to a table and again reading that newly inserted record and over and over again. Is that a normal behavior?
DECLARE Cur_Trans CURSOR FAST_FORWARD READ_ONLY LOCAL
FOR
SELECT -1*Col1
FROM Table1
WHERE Col2 > '10 September 2015'
OPENCur_Trans;
FETCH NEXT FROM Cur_Trans INTO @Param
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC dbo.Proce @Param; --This procedure performs an insert into Table1
FETCH NEXT FROM Cur_Trans INTO @Param
END;
CLOSE Cur_Trans;
DEALLOCATE Cur_Trans;

Viewing 8 posts - 1 through 7 (of 7 total)

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

Change the FAST_FORWARD option to FORWARD_ONLY STATIC.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Emil B SSCertifiable Points: 5568 More actions · Answer 2

Luis Cazares (9/10/2015)
Change the FAST_FORWARD option to FORWARD_ONLY STATIC.

Thanks Louis, that solves the problem. Just got a bit confused whit this behavior.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

STATIC cursors create a copy of the data to keep it the way it was at the creation of the cursor. If not used, the cursor will continue to read the table and might continue to loop forever.

I would expect a FAST_FORWARD cursor to be static, but they're not.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Emil B SSCertifiable Points: 5568 More actions · Answer 4

Thays what I was expecting. Thanks for the explanation Luis

Emil B SSCertifiable Points: 5568 More actions · Answer 5

I was reading https://msdn.microsoft.com/en-us/library/ms180169%28v=sql.110%29.aspx

and was trying to find out what is the default behavior but it only says that:

If FORWARD_ONLY is specified without the STATIC, KEYSET, or DYNAMIC keywords, the cursor operates as a DYNAMIC cursor. When neither FORWARD_ONLY nor SCROLL is specified, FORWARD_ONLY is the default, unless the keywords STATIC, KEYSET, or DYNAMIC are specified.

So if i don't specify FORWARD_ONLY nor SCROLL it will use FORWARD_ONLY as default, therefore the first sentence becomes true and it behaves like i would have specified FORWARD_ONLY?

Luis Cazares SSC Guru Points: 183698 More actions · Answer 6

Emil B (9/11/2015)
I was reading https://msdn.microsoft.com/en-us/library/ms180169%28v=sql.110%29.aspx
and was trying to find out what is the default behavior but it only says that:
If FORWARD_ONLY is specified without the STATIC, KEYSET, or DYNAMIC keywords, the cursor operates as a DYNAMIC cursor. When neither FORWARD_ONLY nor SCROLL is specified, FORWARD_ONLY is the default, unless the keywords STATIC, KEYSET, or DYNAMIC are specified.
So if i don't specify FORWARD_ONLY nor SCROLL it will use FORWARD_ONLY as default, therefore the first sentence becomes true and it behaves like i would have specified FORWARD_ONLY?

It depends on the other options that you choose. If you define it as static, the default is scroll.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Emil B SSCertifiable Points: 5568 More actions · Answer 7

Yes but if i won't specify a thing it will be FORWARD_ONLY DYNAMIC?