Cursor behavior in SQL Server vs Sybase

Question

We are doing a Sybase to SQL Server migration. In SYbase we have Nested Cursors being used. The behavior of SQL Server and SYbase is NOT the same. The problem is,  SYbase the cursor seems to fetch data while open the cursor. Do we have any setting in SQL Server to get the same behavior? Any help is highly appreciated. THe code to repro the issue is here

alter procedure test_cursor
as
declare @tp int
declare c1 cursor DYNAMIC /* we tried with both STATIC and DYNAMIC */
for select type 
	from systypes 
	where name in ('char','varchar','nchar','nvarchar')
	
declare c2 cursor DYNAMIC
for select name + CONVERT(char(5),type)
	from syscolumns where type = @tp -- This variable will have value from C1 but C1 is still not opend
	
begin
open c1
fetch c1 into @tp
print 'outside first cursor''s fetch '+convert(varchar(10),@tp)
while(@@FETCH_STATUS = 0)
begin
print 'inside first cursor''s fetch '+convert(varchar(10),@tp)
 
declare @nm varchar(255)
open c2
fetch c2 into @nm
print 'outside second cursor''s fetch'
print 'Column: '+convert(varchar(10),ISNULL(@tp,'NULL'))+' , '+ISNULL(@nm,'NULL')
while(@@FETCH_STATUS = 0)
begin
print 'inside second cursor''s fetch'
print 'Column: '+convert(varchar(10),ISNULL(@tp,'NULL'))+' , '+ISNULL(@nm,'NULL')
fetch c2 into @nm
end
close c2
fetch c1 into @tp
--deallocate c2 /* incase if the cursor is declared inside the loop, use this deallocate statement */
end
close c1
deallocate c1
deallocate c2
end
go

---

MCITP, MCTS, MCDBA,MCP

Answer 1

I don't know what you expect, but the code makes little sense to me. You have

declare c2 cursor DYNAMIC
for select name + CONVERT(char(5),type)
    from syscolumns where type = @tp

But at this point @tp has not been assigned, so c2 will not hit any rows.

Below is a proper version - or at least one that makes sense.

Some notes:

1) I've moved the FETCH to be first in the loop, so you only need one. This is best practice no matter you are on Sybase or SQL Server.
2) I've made the cursor STATIC LOCAL. You should always use this, unless you know what you are doing and you need something else. Hint: the default dynamic cursor is in no way simple to understand.
3) I moved the declaration of c2 inside the outer loop. This is the normal way to run nested cursors. DECLARE and OPEN before the inner loop, and DEALLOCATE after. (CLOSE is redundant, unless you want to restart the cursor from the beginning.)

create procedure test_cursor
as
SET NOCOUNT ON
declare @tp int
declare @nm varchar(255)

declare c1 cursor STATIC LOCAL
for select type
    from systypes
    where name in ('char','varchar','nchar','nvarchar')

open c1

while 1 = 1
begin
   fetch c1 INTO @tp
   if @@fetch_status <> 0
      BREAK

   declare c2 cursor STATIC LOCAL
   for  select name + CONVERT(char(5),type)
    from syscolumns where type = @tp

   OPEN c2

   while 1 = 1
   begin
      fetch c2 into @nm
      if @@fetch_status <> 0
         BREAK
       print 'Column: '+convert(varchar(10),ISNULL(@tp,'NULL'))+' , '+ISNULL(@nm,'NULL')
    end
    deallocate c2
end
deallocate c1
go
EXEC test_cursor
go
DROP PROCEDURE test_cursor

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

Sorry Erland. I agree that the code does not make sense but i wanted to simulate what happens in Sybase. In sybase the cursors are declared at the top and still the 2 nd cursor gets the value from first cursor.  It looks like in sybase , the cursor's select statement fires NOT in declare statmenet but in OPEN+Fetach i guess. not sure. But the way sybase code work surprise me.

So i just wanted to simulate that code. Currently we are rewriting the code as we do in sql server. that option is there but there are many sps with such usage. I would like to know if there is any setting or someway we can make SQL Server behave like Sybase method.

I cannot copy paste the code. I will get some other script which is more meaningful.

Thanks once again for the comment and sorry for not being descriptive

Thanks

Madhu

---

MCITP, MCTS, MCDBA,MCP

Answer 3

Since cursors were added in SQL 6.0, and a lot of the new features in 6.0 also were introduced in Sybase 10, one can suspect that Microsoft got the enhancements from Sybase. Thus, one would exepct the behaviour to be the same.

And indeed, when I test in SQL 6.5, the loop seems to work the way you described in Sybase.

I don't have the time to research this more for now, but I'll see if I can dig out more. Although, I advice you to keep on rewriting those cursors....

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

Thanks a lot Erland. :)..  Ya we are rewriting the code there is no other go. But may be SSMA should address this. Shall i raise a connect ticket? Basically, the SSMA report does not report this as Manual change needed which is a problem from estimation perspective. We did not foresee this and we have to do lot of manual change now.

---

MCITP, MCTS, MCDBA,MCP

Answer 5

I'm not at all acquainted with SSMA, but you could always try a Connect item?

My guess is that this change is due to the demise of sysprocedures; that was a system table, which held an internal representation of the stored procedures. I'll do some more research on this tonight.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 6

I tested a little more, and the behaviour in SQL 7 SP4 is the same as in modern versions. Furthermore, it does not help setting the compatibility level to 65 in neither SQL7 nor SQL 2005.

So it seems that Microsoft somehow did not notice that they introduced this behavioural change in SQL 7.

Or they just considered the change a bugfix. :-)

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 7

Thanks a lot for the detailed troubleshooting  Erland. I have forwarded a mail to [e-mail] . Let me see what they wants to say.  The problem is, since it is not reported in SSMA report (SSMA reports how much manual intervention is required whil migrating sybase code to sql server) we dont estimate and we will have many fold manual intervention than planned. At least SSMA should be able to report this issue if not solve while conversion so that it can be estimated.

Thanks once again for all the help Erland.  Hats off to you for helping in so detailed manner. Even product team may not be able to do this in the given time.

thanks

Madhu

---

MCITP, MCTS, MCDBA,MCP