I think you will find that folk here will recommend avoiding cursors. I definitely would not leave a Cursor (or a transaction / lock for that matter) open during a user-operation (where they might go to lunch, or on holiday ... or their PC crash / be turned off without a clean shutdown, and they have no way to "cancel" the transaction)
If you want to ADD a record then use INSERT (with a check that it does not exist) - but that's really the SAVE option AFTER and ADD - "ADD" can just display a blank screen.
EDIT requires a SELECT to get the data and then a SAVE to save it.
The only difference in these two SAVE operations is whether they expect to replace and existing records, or create a new one.
We have a column in every table (called EditNumber, but Version or anything similar would do; you could also use a Row Version GUID which is available in SQL, and automatically changes whenever the record is updated) which is incremented on each update. We store that in the Form, and then pass it back when the user presses SAVE and then compare that to the current record. If the EditNumber is different then someone else already saved that row.
DELETE - again, we use our EditNumber to check that the record being deleted is the same [version] as the user has on their screen, during the DELETE process.
CANCEL - nothing to do here
FIRST - SELECT TOP 1 Col1, Col2, FROM MyTable ORDER BY SequenceCol1,SequenceCol2
LAST - Same, but DESC sort
NEXT - SELECT TOP 1 Col1, Col2, FROM MyTable WHERE PKey > @CurrentRecordPkey ORDER BY SequenceCol1,SequenceCol2
PREVIOUS - Same, but DESC
You might want to Google for CRUD SQL scripts - Create, Read, Update, Delete
Our "SAVE" routine is an UpSert. If will insert if the record does not exist, or update if it does. There is an optional flag indicating "Record must NOT exist" / "Record MUST exist" so we can force Create/Update when the scenario is known ... but we have situations where we just want "Don't care if it exists, just overwrite whatever version of the record you have, or create a new one"
There are issues to do with contention and race conditions that I recommend you consider when building your generic solution and/or SQL Script templates. e.g. someone else is adding the same record, and they do that BETWEEN you CHECKING that it does not exist and then INSERTING the record (i.e. your INSERT will fail). So you definitely do NOT want to do:
- Check is exists
- FALSE = INSERT
- TRUE = UPDATE
Between 1 & 2 someone else may insert it
Between 1 & 3 someone else may delete it
You can lock the record between 1&2/3 or, my preference, you can do
- INSERT if not exists
- IF @@ROWCOUNT = 0 THEN UPDATE
or
- UPDATE if exists
- IF @@ROWCOUNT = 0 THEN INSERT
generally the first is used if it is expected that the majority of records will NOT exist, and the second if it is more likely that the record ALREADY exists.