Help with Cursor please

Question

Help with Cursor please

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Adam Machanic SSCoach Points: 15370 More actions · Answer 1

Noeld,

What kinds of things do you use it for? I'm quite curious, as I've never had occasion to use it -- but it's brought up fairly regularly by users in forums.

--
Adam Machanic
whoisactive

PW-201837 SSC-Insane Points: 20805 More actions · Answer 2

Here's an example. Note, I'm not saying there aren't SQL alternatives, but this is a current implementation:

Problem. Person has 1 or more bank accounts at 1 or more branches. For any given time period snapshot, an indicator of "Primary Branch" is requried, with the business rule being branch with most funds under admin (FUA). If a person has accounts at 2 or more branches and there is a tie for FUA, take lowest numbered branch.

Original solution was to SELECT INTO a temp table with an Identity column, insert into it ordered by person, descending FUA, ascending branch.

Once done, the "Primary Branch" for each member is the record with Min(IDENTITY) per PersonID.

There are alternatives with sub-queries & virtual tables. Which I would have to port the code to if the outcome of this is that the Identity sequence isn't guaranteed to match the ORDER BY when use with INSERT.

noeld SSC Guru Points: 96590 More actions · Answer 3

Adam,

In 'details' type of tables I usually implement an Identity field and all is good ordering by the Identity value but the User wants to see 1,2,3... not 345,456,585, on some other schemas I have seen creation time used for that purpose and same thing goes at front end.

At some places you need to INCLUDE ties like in select TOP .... WITH TIES and you want to show a record number.

I am positive that all that can be done at front end but I was a consultant and they had a budget so front end modification was not followed (too many reports)

And even when there are other options this one is very easy to understand and therefore maintain

Cheers,

* Noel

Adam Machanic SSCoach Points: 15370 More actions · Answer 4

Update on this:

I received two replies. One said that CREATE TABLE w/ IDENTITY then INSERT w/ ORDER BY is, indeed, guaranteed. Another said that it's only guaranteed if you use the (MAXDOP 1) query hint.

Personally, I would use the latter -- for now -- just to be on the safe side, as I still don't trust this... Had it ground into my head too many times that it's not guaranteed. It's going to take me some time to wrap my head around thinking that it might actually be a usable technique

--
Adam Machanic
whoisactive

PW-201837 SSC-Insane Points: 20805 More actions · Answer 5

Thanks for getting back to us on it.

Thankfully this will all go away when we get ROW_NUMBER() OVER() in Sql2005.

Adam Machanic SSCoach Points: 15370 More actions · Answer 6

No doubt! The ROW_NUMBER function is very useful.

Also, I updated my article

http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx

--
Adam Machanic
whoisactive