Cursor behavior in SQL Server vs Sybase

Objective is to be, if one cursor’s input is based on the other cursor. We should execute the input cursor with prior and input cursor’s script want to come first in the procedure

Cursor interdependency based on parameter supported by Sybase, even the inner cursors declare statement placed next of the outer cursors declare statement. But Cursor interdependency based on parameter not supported in SQL server, if the Cursor declaration is not done inside the outer cursor’s open statement. We will get the different result set for the same logic code based on the Sybase and SQL Server. If we made the code changes to declare the Inner cursor inside the Outer cursor (between Open and close block), we can get the same result set. I have given detailed explanation below along with screenshot.

Sybase Script with Cursor Inter-dependency

Here first cursors output is the input for the second cursor. But the declaration of the second cursor located before the first cursors open statement. Though Sybase maintains the Inter-dependency between the cursor and passes the first cursor’s output to next cursor’s input. So the Sybase output is based on the two cursor’s input and output. You can see the output of the Sybase below.

One more is, Cursor de-allocation handled by Sybase engine automatically. But In SQL Server we should give the de-allocation explicitly for the required places.

Sybase Output for the above given sample code

To achieve the same result set in SQL Server we should declare the Second (Inner) Cursor declaration and open statement inside the First (Outer) Cursor’s Open statement. Otherwise SQL Server engine will gives the NULL value for the @var2 and only give the below result set.

SQL Server output with bug 

SQL Server Script with relevant changes 

Here we given De-allocate statement for each cursor explicitly for the required places .Outer cursors output will simply flow as a inner cursor’s input. If we made the code changes like above we can get the same result as like as Sybase. Once again I attached actual result for your reference.

SQL Server Output with relevant changes 

Please click here to know about sybase to sql server migration issues