SQLTeam.com | Weblogs | Forums

SQL Server Cursor Fetch Commands


#1

Hi Guys,

I've begun looking at using a cursor as a means if record navigation for my application. It is a basic data entry form which uses buttons for all the basic functions Add, Edit, Update, Delete, Cancel, First, Last, Next and Previous. I know that a cursor provides those basic navigation functions using Fetch. My question is how to tie them to the associated buttons. Any thoughts or suggestions you might have would be helpful. Thanks in advance.


#2

There is so much wrong here...

  1. Button click events in your application simply cause stored procedures to execute... Nothing more (at least not in this context).
  2. Cursors should be the very last option you consider when trying to solve a problem as they will almost always be the least performant option. When possible a set based solution is always preferable.

#3

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:

  1. Check is exists
  2. FALSE = INSERT
  3. 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

  1. INSERT if not exists
  2. IF @@ROWCOUNT = 0 THEN UPDATE

or

  1. UPDATE if exists
  2. 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.


#4

Thanks for the insights guys, Much appreciated. Firstly this is NOT a multi user App this is just me at my computer entering updating and deleting. This is NOT a production app. This is an app I've created for myself as a means to expand my knowledge of both VB.Net and SQL Server. So I don't care about performance issues or how elegant the code is. I care about it working or not working. The buttons were listed to give a complete picture of what I'm doing not because I need an answer for all of them. Cancel will be a rollback. Improvements will come as I gain more experience just as any developer refines his/her coding as they become more proficient. I'm well aware that cursors are dogs performance wise. Given it's a small database with only about 10,000 records and it won't be getting bigger but will in fact most likely be getting smaller as duplicate card information is deleted..


#5

You keep on ignoring the advice we give you. Your prerogative of course, but a waste of your time asking the questions and our time answering them


#6

I'm not ignoring your advice. I asked a specific question Kristen. You chose to ignore my question and offer your own solution. When I ask a question I have a reason for asking it. I'm not trying to be disrespectful in any way. I've listened to all your advice and I intend to make good use of it. You didn't answer my question instead you gave me alternatives. Whose fault is that? When I ask a specific question I expect a specific answer. Are you more experienced than me with Server, yes by all means and I bow to your experience hands down. Did you ever stop to think that there's a reason or a method to my madness? No. No problem. I'll figure out the answers on my own


#7

Yours... It's your fault. You came here asking the SQL equivalent of, "I'm moving from one house to another, Rather than loading boxes into a moving van and moving everything in a single load, I've decided to unpack at the old house and drive each item to the new house one item at a time, please show me how to do that". The fact is we wouldn't do that and there's no point in telling someone w/ less experience how to do that. The people who post answers here have made careers out of systematically avoiding unneeded expenditures.
The better question would be framed as "Here is where I'm at... This is where I need to be... These are the the specific requirements than need to be factored into the solution,,, Here id DDL and some representative sample data... Based on the sample data, this is my expected output... "

If that's your expectation, you're in the wrong place. You need to reach into you pocked, grab your wallet and call a consultant. The people who answer questions do not get paid for the service they provide and are under no obligation to answer your specific questions as you have them written.

I've seen a few of your other posts and I get the impression that you have a have an OOP programming background. I've been fortunate enough to work directly with some very talented OOP programmers (C#.net mostly) and the one thing they all seem to have in common is their instinct to use loops & cursors to iterate through data. I'm not a OOP programmer myself, so I won't attempt to say why they have that mindset, it's just an observation I've made over the years. While iterating though data, one row at a time may work fine at the application tier, that's simply not the case at the database tier.

The only reason @Kristen and I have made the comments that we've made, is because we'd like to keep you from forming one of the nastier "bad habits" that we've seen others fall into.


#8

The following may be relevant (tangentially) in this context:

If I say something here that people agree with there will usually be no indication of that. Just silence. Yeah, there is a LIKE button but its almost never used here.

But on the fip side if I say something that other people disagree with, have alternative suggestions, or they spot a significant typo etc., then they will definitely say something.

I mention this because the forum software here is somewhat unusual and does not provide the sort of feedback you get on other forums to indicate if an answer has any support ... or not.

Good luck with your project. please reflect on the fact that the way you are proceeding is based on existing procedural programming skills, but what is needed is set-based thinking, and you are heading for a whole pile of bad habits and a stack of inappropriately written code that will be an increasingly large burden to re-factor at some future point.

The help that I thought I could give clearly doesn't meet with what you want, so I'm done here.