Hi, My cursor stops after hitting a rollback. How can i continue processing after a rollback occurs? Thanks, Greg Here is my code: DECLARE @supplied_date DATE SELECT @supplied_date = '2014-12-10' //Change the supplied date DECLARE service_fut_curs CURSOR FOR SELECT service_code FROM service_future WHERE effective_date > @supplied_date
|
By default, cursors are closed at the end of transaction (commit or rollback). When you open a statement, you can specify that it be opened "WITH HOLD" to indicate it should remain open after the transaction. Be aware that the semantics of a cursor being held open past a transaction, particularly after a rollback, are unusual and require special attention. The option ansi_close_cursors_on_rollback can be set to force even WITH HOLD cursors to close on ROLLBACK (this is the behaviour defined by the ANSI SQL Standard for WITH HOLD cursors). Some of these semantic issues can be sidestepped if you use an INSENSITIVE cursor type that would take a copy of the data at open time and not be sensitive to changes in the values of the referenced tables. This is not in your question, but I occasionally am surprised when converting a FOR loop into an explicit cursor and forget to include WITH HOLD. The FOR loop implicitly opens the cursor with WITH HOLD and it will not close automatically after a commit. The cursor may be closed after a ROLLBACK depending on the ansi_close_cursors_on_rollback option. Thanks Ivan, I was wondering if the there is another approach to handle this process. For each row that meets the cursor criteria, I need to:
Thanks, Greg
(12 Jan '15, 12:39)
GregC
Replies hidden
This may be a good use case for DML-derived tables [1]. You could use one statement to delete rows from one table (service) and insert into the target (history). I haven't personally used the feature so I haven't crafted an example. I am not sure whether DML-derived tables can be used with the TSQL dialect (it appears your example uses TSQL dialect). [1] http://dcx.sap.com/index.html#sa160/en/dbreference/from-statement.html*d5e55728
(12 Jan '15, 20:14)
Ivan T. Bowman
As we are using SQL, would it not be easier to use set statements, i.e. instead of using a cursor "one row at a time" approach, do all the "move" operations for all according row in one go? One approach to do so would be to define a temporary table to identify all according rows (e.g. all "service_code" values) and then use joins to insert/delete all according rows from the according tables. (The temporary table would only be required if the matching rows may not be identified lateron, i.e. when the "moves" themselves would prevent the rows to be filtered again.) Apparently, that would usually mean to use one transaction for all rows whereas a row-by-row approach might allow several rows to be moved whereas other row moves may be rolled back. This is just a very general hint, without knowing the table schema (and particulary the FK relationship between tables "service", "service_future" and "service_hist" it's difficult to tell whether this will work for you... Another approach would be to include all rows in the same table and use a status field to tell whether these are "historical" or "current" or "future" entries - "moving" would then be simplified to modify the status field.
(13 Jan '15, 03:52)
Volker Barth
|