SQL with Manoj

–> Question:

In MSDN help mentions static cursor as follows:

STATIC: Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications

It say’s that modifications is not allowed in the static cursor.

I have a questions regarding that:

declare ll cursor global static
for select name, salary from ag
open ll
fetch from ll

close ll
deallocate ll

In “AG” table, “SALARY” was 100 for all the entries.

When I run the Cursor, it showed the salary value as “100” correctly.

After the cursor was closed, I run the query select * from AG.

But the result had updated to salary 200 as given in the cursor.

Help file says modifications is not allowed in the static cursor. But I am able to update the data using static cursor.
 

–> My Answer:

MSDN meant that:

“After you create a STATIC Cursor if the table is updated by any other process that update will not be reflected in the Static Cursor rows.”

1. Let’s say your SPID=100 and you read the table with Salary=100.

2. You created a STATIC Cursor and read the above table with Salary=100.

3. Another SPID=105 updated that Salary=200

4. But when you will read the cursor it will still show you Salary=100.

But yes you can update the same table form the Cursor itself as you are doing above. Check this MSDN link for clear info: https://msdn.microsoft.com/en-us/library/windows/desktop/ms678392%28v=vs.85%29.aspx?f=255&MSPPError=-2147217396
 

Ref Link.