Last post Dec 27, 2015 04:20 PM by PatriceSc
Dec 27, 2015 12:10 PM|benglish|LINK
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
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
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
SELECT FirstName,LastName,isOrganization FROM dbo.Party
FETCH NEXT FROM mycursor
UPDATE dbo.Party SET FirstName=N'test'
FETCH NEXT FROM mycursor
TNX in advance.
Dec 27, 2015 12:48 PM|PatriceSc|LINK
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.
Dec 27, 2015 01:36 PM|benglish|LINK
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
Dec 27, 2015 04:20 PM|PatriceSc|LINK
#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.