locked
Static Cursor applies UPDATE! RRS feed

  • Question

  • User-1887133141 posted

    Hi pals, 

    As it is mentioned in the documentations of Static Cursor (https://technet.microsoft.com/en-us/library/ms191286(v=sql.105).aspx), this cursor is READ_ONLY, i.e. it is not possible to update or insert any record in the table. With this in mind, I have created a table named "Party" using the following code:

    CREATE TABLE [dbo].[Party](
     [PartyId] [int] IDENTITY(1,1) NOT NULL,
     [FirstName] [nvarchar](50) NULL,
     [LastName] [nvarchar](50) NULL,
     [Title] [nvarchar](50) NULL,
     [NationalIdentification] [nvarchar](50) NULL,
     [isOrganization] [bit] NULL,
    PRIMARY KEY CLUSTERED 
    (
     [PartyId] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO

    Then, I created a static cursor and update the FirstName column of all records, and it did happen with no error! Would you please help me why it is possible to update the table, while Static Cusror is READ_ONLY?

    Here is my cursor

    DECLARE @lname nvarchar(50),@fname nvarchar(50),@isorg BIT
    DECLARE mycursor CURSOR
    static
    FOR
    SELECT FirstName,LastName,isOrganization FROM dbo.Party
    OPEN mycursor
    FETCH NEXT FROM mycursor
    INTO @fname,@lname,@isorg
    WHILE @@FETCH_STATUS=0
    BEGIN
    
    UPDATE dbo.Party SET FirstName=N'test'
    
    FETCH NEXT FROM mycursor
    INTO @fname,@lname,@isorg        
    end 
    CLOSE mycursor
    DEALLOCATE mycursor
    

    TNX in advance.

    Yours Sincerely,

    Benglish

    Sunday, December 27, 2015 12:10 PM

Answers

  • User753101303 posted

    #1 seems some kind of misunderstanding. You are using a cursor to READ the data. You are NOT using this cursor to update the table but you just use another unrelated SQL update statement. If you want to use the cursor to do the update then you have to use CURRENT OF (and then you should have an error message).

    #2 if you want to use the cursor, use CURRENT OF. Else you are just using a SQL update statement which is unrelated to the cursor.

    Also keep in mind that cursors should be avoided unless really needed so you'll likely rarely use them and even less often for updates. Do you have an actual use case? Else I wouldn't spent too much time on this.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, December 27, 2015 4:20 PM

All replies

  • User753101303 posted

    Hi,

    You are not allowed to update this table through this cursor. Here you are just using a separate unrelated SQL statement. See https://msdn.microsoft.com/en-us/library/ms177523.aspx and the WHERE CURRENT OF clause (so it should be something such as UPDATE dbo.Party SET FirstName=N'test' WHERE CURRENT OF mycursor) if you want to test updating through the cursor. And so if using a CURRENT OF clause, it should fail.

    Sunday, December 27, 2015 12:48 PM
  • User-1887133141 posted

    Dear PatriceSc,

    Firstly, thanks for your reply. I have 2 question regarding your previous answer:

    1. If I am not allowed to use UPDATE/INSERT/DELETE in static cursor, so why SQL Server does not show me any error and always does the command?!

    2. WHERE CURRENT OF clause must always be used in all cursors instead of usual WHERE clause?

    TNX in advance

    Sunday, December 27, 2015 1:36 PM
  • User753101303 posted

    #1 seems some kind of misunderstanding. You are using a cursor to READ the data. You are NOT using this cursor to update the table but you just use another unrelated SQL update statement. If you want to use the cursor to do the update then you have to use CURRENT OF (and then you should have an error message).

    #2 if you want to use the cursor, use CURRENT OF. Else you are just using a SQL update statement which is unrelated to the cursor.

    Also keep in mind that cursors should be avoided unless really needed so you'll likely rarely use them and even less often for updates. Do you have an actual use case? Else I wouldn't spent too much time on this.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, December 27, 2015 4:20 PM