DECLARE CURSOR ....FOR UPDATE OF

[SimonTheSponge]

Hi I'd like to create a cursor that I can Fetch through and update the contents of source tables; but I'm struggle to find an example of an updatable cursor anywhere in the help to get an idea on the syntax. The Help just lists the initial syntax

DECLARE cursor_name CURSOR
[LOCAL | GLOBAL]
[FORWARD_ONLY | SCROLL]
[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
[TYPE_WARNING]
FOR select_statement
[FOR UPDATE [OF column_name [,...n]]]

Has anybody any ideas ?

 

[SQLSister]

Never do an update with a cursor. Cursors are terrible performance hogs and should be avoided at all costs. It is very rare that they would be needed for an update. Use the update withthe select clause instead
Update examples that might give ideas of how to do this in a set based fashion:

UPDATE Table1
SET Field1 = Table2.Field2*100
FROM Table1 join Table2
ON Table1.IDField = Table2.IDField
WHERE Table2.Field3 = 'sometext' and table1. Field1 is null

UPDATE Expenses
SET NEWRc = Managers.RC
FROM  RCMap INNER JOIN Managers 
ON RCMap.UID = Managers.UID 
INNER JOIN Expenses 
ON RCMap.OldRC = Expenses.RC

UPDATE Job
SET ClientID=(SELECT Invoice.ClientID
              FROM Invoice
              WHERE Job.JobID=Invoice.JobID)
WHERE ClientID=0

UPDATE Table1
SET Field1 = Table2.Field2*100
FROM Table1 join 
(SELECT IDFIELD, Field2 FROM table3 join table2 
ON Table3.IDField = Table2.IDFIELD 
WHERE table3.field4 <> 'Complete') Table2
ON Table1.IDField = Table2.IDField

INSERT Table1 (Field1, Field2, Field3)
Select field4, Fiedl2, Field5 from table2 where Field6 > Getdate()

UPDATE Table1
SET Field1 = CASE 
         WHEN table2.price IS NULL THEN 0
         WHEN table2.price < 10 THEN table2.price*1.1
         WHEN table2.price >= 10 and table2.price < 20 THEN table2.price*1.3
         ELSE table2.price*2
      END
FROM Table1 join Table2
ON Table1.IDField = Table2.IDField
WHERE Table2.Distributor = 'FIS' and table1.Field1 is null

UPDATE Table1
SET Field1 = CASE 
         WHEN T2.TotalPrice IS NULL THEN 0
         WHEN T2.TotalPrice < 10 THEN table2.TotalPrice*1.1
         WHEN T2.TotalPrice >= 10 and table2.TotalPrice < 20 THEN table2.TotalPrice*1.3
         ELSE T2.TotalPrice*2
      END
FROM Table1 join (SELECT CustID, Sum(price) as TotalPrice FROM  table2 
Group BY CustID) T2
ON Table1.IDField = T2.CustID

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[SimonTheSponge]

Thanks, that probably explains why I've never seen it used anywhere ;-)

 

[SQLSister]

Oh thtere are people who use it, they just have poorly operating systems.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[Josiefuz]

There are reasons to use cursors -- even updateable ones. Not every operation can be performed via set-oriented approaches. I agree that cursors are to be avoided, but in some cases they simply can't be.

 

[SimonTheSponge]

Have you a working example of how one might use a Cursor in this fashion ?

Thanks

 

[Josiefuz]

Hi Simonhorne,

Here's one I use a lot in data migrations. The scenario is that I am building a database that is used by an application that is rather restrictive in the required database setup. I need to create new rows that have a 16-character single-column unique PK that will work in a distributed environment. The single-column distributed requirement rules out identity columns, and the column is too short for a automaticly generated GUID. The solution I have is a stored proc that creates a key based on a timestamp and counter combination. The only way to produce many PKs via that stored proc is to use a cursor. Here's an example:

/* populate the PKs for #account */
/* assign new pks */
DECLARE @pk varchar(16)
DECLARE cur_row CURSOR
FOR
SELECT a.account_id
FROM #account a
FOR UPDATE

OPEN cur_row
FETCH NEXT FROM cur_row INTO @pk

/* loop through each attachment row */
WHILE (@@FETCH_STATUS=0) BEGIN

EXEC usp_PkMaker @pk OUTPUT
UPDATE #account SET
account_id = @pk
WHERE CURRENT OF cur_row

/* get next attachment row */
FETCH NEXT FROM cur_row

END /* cursor loop */

/* close and deallocate cursor */
CLOSE cur_row
DEALLOCATE cur_row

//--------------------------------------//

Another example that I ran into once was the need to read in set of rows with a column that held RTF data. I needed to convert that data to plain text and store just the plain text back into the original column. This all neeed to happen in a trigger when the data was initially saved. The only way I found to convert the text via SQL was to write a VB DLL and call it as an object from the SQL Server. The DLL accepted an RTF string and returned the plain text version. It's a long script, but here's the basic parts of it:

DECLARE cur_row CURSOR
FOR
SELECT activity_stamp2
FROM #archived_email_activities
FOR UPDATE

OPEN cur_row
FETCH NEXT FROM cur_row INTO @pk

--reset counter
SET @recipient_counter = 0

--loop through each activity row
WHILE (@@FETCH_STATUS=0) BEGIN

--parse send_to
set @s = (
SELECT cast(send_to as varchar(8000))
FROM dbo.activity a
WHERE a.stamp2 = @pk)

--strip the rtf control characters from the string
SET @rtf_txt = @s
EXEC sp_OAMethod @object, 'StripRtf', @plain_txt OUTPUT, @rtf_txt OUTPUT
SET @s = @plain_txt

...do more parsing and then close/deallocate cursors

//-------------------------------------//

A third example is doing complex parsing such as taking a bunch of email addresses that are stored in one column and breaking those out into distinct rows. In this case it's necessary to loop through a particular column pulling out string values as you go. I'm not sure there is a way to accomplish this without a cursor, but at the time I wrote the procedure I determined that even if there was, it would be so convoluted that it wouldn't be as maintainable as a cursor. Given the low volume, cursor performance wasn't an issue.

Like I said before though, I don't advocate cursors when there is a better set-oriented solution available. But not all database functions are set-oriented.

If you have a set-oriented approach to either of these (especially the PK scenario) I'd love to hear it as I have been searching in vain for a way to get rid of that particular cursor.

 

[donutman]

SQLSister, you're going to have to do this one...I did the last two. I think all three are doable. I'm headed for Florida early tomorrow morning so I don't have time for this!

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SimonTheSponge]

Thanks Josiefuz,

I think the "where Current of cur_row" was the info that filled in the missing piece of the puzzle.

My interest in this was one of pure curiosity - seeing a piece of optional syntax in help and being rather curious and then frustrated not to find an example of it - isn't it just always the way ;-)

I do appreciate SQLSister's comments and can appreciate the locking implications of this type of update, although in your first example, problem of not having a PK in your original temp table obviously rules out an UPDATE <table> SET x= ....

Thanks everyone, this has been useful

 

[donutman]

Ok, I'm in Austin for a day. You can eliminate the cursor AND there's a much better algorithym for creating a semi-globally unique PK, i.e. one that is less likely to become non-unique. I say semi-globally because it's beyond my ability to re-invent a GUID in char(16). First give us a little background.
[ul][li]How many potential servers are there?

[/li][li]Are they known to you or can new ones just appear?

[/li][li]How large will the merged table (or the maximum number of rows at one server if they aren't merged) eventually grow to?[/li][/ul]
-Karl





[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Josiefuz]

I'd love to see your algorythm and if it'll work in my environment I'll even dance a little jig in your honor!

>>How many potential servers are there?
It varies depending upon the system, but as remote users act as their own servers (via MSDE)it is generally less than 2000.

>>Are they known to you or can new ones just appear?
As new users are added new "servers" appear, so it's known at any point in time, but will vary over time.

>>How large will the merged table (or the maximum number of rows at one server if they aren't merged) eventually grow to?

Again it varies by table, but it rarely goes over 500k rows (merged).

The current algorythm produces a timestamp+counter in this format:

YYMMDDHHmmSScccc (i.e. 0502051629301234)

cccc is a number 0000-9999 and just rolls back to 0 at 10000. It's not a perfect GUID either, but it's unique enough for the application.

The whole 16-character number is stored as a varchar(16) because the application that uses the same database drops new PKs using a different timestamp+counter algorythm that is then converted to hex values that are 16 charatcters long and stored as a string.

 

[Josiefuz]

Just an added comment. The current resulting stamp is sufficient and works fine. What I ran into when I wrote it was that I couldn't find a way to get the counter working (it uses a counter table) in an UDF. Since I couldn't get it into a UDF I couldn't find a way to assign the pk without using a cursor loop that calls the SP repeatedly.

 

[donutman]

The whole 16-character number is stored as a varchar(16) because the application that uses the same database drops new PKs using a different timestamp+counter algorithm that is then converted to hex values that are 16 characters long and stored as a string.

I'm not sure what that implies...are your PKs transformed or are you just saying that the PKs that the application creates PKs that won't conflict with yours?
Aside from that issue, here are two alternate algorithms to choose from depending on what you mean by knowing the server. I'm away from the office so I don't have time to research how to have SQL Server get the MAC address of its NIC card, but I'm almost positive that it can be done. Use that as the first 10 characters and the remaining 6 is a counter which nicely covers the 500k rows.
The 2nd alternative is to assign each server a number and use the 4 digits in place of the MAC address. You could use the actual server name, but that's more likely to duplicate.
Now to eliminate the cursor you need to insert the rows you have into a different temp table that has all the columns of the final table except the PK. Make that an identity column. Then do the final insert using the identity column but cast it into a CHAR(16) column that includes your Server ID from above. If there is an initial seed issue, then you simply add the current MAX(ID) to the identity value before you convert it to CHAR(16).
I'm very surprised that you haven't had a duplicate PK using the time. With 2,000 servers it's got to happen sooner or later.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[dbomrrsm]

Hope your having a nice time on Florida Karl

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[donutman]

Thanks DB. Actually, I'm headed there (from Austin), but will be stopping off (tomorrow) in New Orleans for Fat Tuesday. Have to check out the authentic Paczki's (a donut of sorts)...business trip of course.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]