IF condition in CURSOR

Question

IF condition in CURSOR

Viewing 11 posts - 31 through 40 (of 40 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

RBarryYoung (9/4/2009)
Jeff Moden (9/3/2009)
REVERSE? Nah... just wicked dyslexic this time of day. 😛
Hmm, for some reason I always thought that that word was spelled "cixelsyd". 😛

Lysdexia is jo noke!

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

GSquared (9/4/2009)
RBarryYoung (9/4/2009)
Jeff Moden (9/3/2009)
REVERSE? Nah... just wicked dyslexic this time of day. 😛
Hmm, for some reason I always thought that that word was spelled "cixelsyd". 😛
Lysdexia is jo noke!

Don't I wonk it.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

sudhanva Ten Centuries Points: 1343 More actions · Answer 3

Hey Guys,

Thanks for the huge response and fun here. I didnt expect a 4page posts for this topic 😎

The query which has vendor_id etc was just an example, it is not the real query/scenario.

Why i'm using cursor (or curses 🙂 is, i have a Master table, and on its update i have another table where i need to insert/update records.

example: Lets consider, two tables, 1)Country 2)State

Its like, if CountryPopulation column gets updated, i need to run a process in which, population of all the states will get updated, under that country. For this i found cursor as best option. This is just an example to explain you my requirement.

I hope my explanation is not bad here. 🙂

GSquared SSC Guru Points: 260824 More actions · Answer 4

Is there a reason to denormalize it that way, and not just sum up the counties when you need the state total? If that won't work (for performance reasons), then a set-based update will be faster and more efficient than a cursor.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 5

sudhanva (9/4/2009)
Hey Guys,
Thanks for the huge response and fun here. I didnt expect a 4page posts for this topic 😎
The query which has vendor_id etc was just an example, it is not the real query/scenario.
Why i'm using cursor (or curses 🙂 is, i have a Master table, and on its update i have another table where i need to insert/update records.
example: Lets consider, two tables, 1)Country 2)State
Its like, if CountryPopulation column gets updated, i need to run a process in which, population of all the states will get updated, under that country.[font="Arial Black"] [highlight="YELLOW"]For this i found cursor as best option.[/highlight][/font] This is just an example to explain you my requirement.
I hope my explanation is not bad here. 🙂

That's what we're trying to get at ... YOU think that a cursor is the best way and there's a 99% chance that it is not. But we can't help you unless you post some actual code and some data in a readily consumable format. Please see the first link in my signature line below for how to do that.

Heh... I'm going back to the mirror... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 6

sudhanva (9/4/2009)
example: Lets consider, two tables, 1)Country 2)State
Its like, if CountryPopulation column gets updated, i need to run a process in which, population of all the states will get updated, under that country. For this i found cursor as best option. This is just an example to explain you my requirement. I hope my explanation is not bad here. 🙂

I'm sure we can help with this. Just one question: if a country has its population changed, what are the rules for updating the states? Say USA changes to 600 million, how do you know which states to update any by how much?

A more usual solution would be to update the state populations individually. The country population would then be auto-magically updated from the total for the states added together.

I'm interested to help, and I don't need any elaborate setup. Just describe the tables concerned, give some sample entries, and show what the desired output would be.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 7

I'm sure we can help with this. Just one question: if a country has its population changed, what are the rules for updating the states? Say USA changes to 600 million, how do you know which states to update any by how much?

Which does seem the wrong way round, Country population should equal the sum of state population.

Or even consider the idea that you dont need to store the country population at all. Just use sum of the states.

Clear Sky SQL
My Blog[/url]

GSquared SSC Guru Points: 260824 More actions · Answer 8

GSquared (9/4/2009)
Is there a reason to denormalize it that way, and not just sum up the counties when you need the state total? If that won't work (for performance reasons), then a set-based update will be faster and more efficient than a cursor.

Just realized I misread it as county rolling up to state as opposed to state rolling up to country. Same question still applies. Why store the country population at all instead of just summing up the states?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Paul White SSC Guru Points: 150467 More actions · Answer 9

G²! That's quite funny!

Actually, I kinda gave him the benefit of the doubt. For all I know, he's writing a game where you allocate population to a country, and an algorithm is required to sub-allocate that to states. Yeah, I know it's kinda weak, but otherwise I'm out of ideas...

edit: messed up the superscript

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

sudhanva Ten Centuries Points: 1343 More actions · Answer 10

Paul White (9/5/2009)
A more usual solution would be to update the state populations individually. The country population would then be auto-magically updated from the total for the states added together.
I'm interested to help, and I don't need any elaborate setup. Just describe the tables concerned, give some sample entries, and show what the desired output would be.

Thanks.

Forget about country/state population, that was just an example.

Paul, i will write and send the sample query ASAP, with table data etc. That will make your/my job easy.

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 11

Before you do, please read the following article for the best format to present that info in... it'll really help folks concentrate on your problem...

http://www.sqlservercentral.com/articles/Best+Practices/61537/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)