IF condition in CURSOR

Question

IF condition in CURSOR

Viewing 15 posts - 16 through 30 (of 40 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Paul White (9/3/2009)
Just for completeness, this is also valid:
-- Just to simulate some condition or other
declare @v-2 int;
set @v-2 = 2;
-- Dear Lord, why am I using a cursor here?
-- (probably an updatable, global, dynamic cursor at that!)
declare mycur cursor for

The real question is "What is a set-based query?"

Jeff must have seen it by now and had a heart-attack 😀

BTW - best laugh of the day

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

Sudhanva,

Please do not think for a moment that we are making fun of you personally. Obviously, those of us who volunteer to help others have a prejudice against the use of cursors. This is because, as a general rule, they run slowly and are far too often used instead of a set based solution. (They are also evil and should be destroyed, but I am digressing.)

Regular volunteers all come "know" each other, at least from our postings, and we sometimes joke around with each other in these threads. Rest assured, the joking is NOT at your expense.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1003901 More actions · Answer 3

Paul White (9/3/2009)
Bob Hovious (9/3/2009)
-- Dear Lord, why am I using a cursor here?
Your penance is 10 Hail Mary's and 10 Our Father's.
Go and sin no more.
:laugh:
I hope Jeff never sees this thread...

.enots ot nrut t'nod I os rorrim a hguorht siht ta gnikool yllautca m'I ...denrawerof neeb d'I 😉

--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)

Jeff Moden SSC Guru Points: 1003901 More actions · Answer 4

Grant Fritchey (9/3/2009)
Bob Hovious (9/3/2009)
I hope Jeff never sees this thread...
You mean Cardinal Moden of the SQL Inquisition? :w00t::w00t::w00t:
Torquemada wishes he had Jeff's skill with the pork chop.

Heh... he never learned that the secret is in the "torque" of the wrist. I know... not funny... someone just mada me do it. :hehe:

--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)

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

CirquedeSQLeil (9/3/2009)
Paul White (9/3/2009)
Just for completeness, this is also valid:
-- Just to simulate some condition or other
declare @v-2 int;
set @v-2 = 2;
-- Dear Lord, why am I using a cursor here?
-- (probably an updatable, global, dynamic cursor at that!)
declare mycur cursor for
The real question is "What is a set-based query?"
Jeff must have seen it by now and had a heart-attack 😀
BTW - best laugh of the day

BWAA-HAA!!! "Oh my! I have a feeling we're not in Kansas anymore, Toto." And, the Munchins know a whole bunch of SQL curses (or is that "cursors"), too! 😛 (Going back to reading with 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

Bob ("evil and should be destroyed") and Jeff ("!enots ot gninrut") - you guys just rock!

So funny.

😀

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1003901 More actions · Answer 7

Bob Hovious (9/3/2009)
Sudhanva,
Please do not think for a moment that we are making fun of you personally. Obviously, those of us who volunteer to help others have a prejudice against the use of cursors. This is because, as a general rule, they run slowly and are far too often used instead of a set based solution. (They are also evil and should be destroyed, but I am digressing.)
Regular volunteers all come "know" each other, at least from our postings, and we sometimes joke around with each other in these threads. Rest assured, the joking is NOT at your expense.

Sudhanva,

Bob is absolutely correct. We're mostly poking fun at Paul for writing a "correct" cursor (two, actually). He's also correct in saying that, as a general rule, cursors are a bad thing to do in SQL.

With that in mind, if you explained more of what you were trying to do and maybe even posted the problem that you're trying to solve with cursors, we might be able to surprise you with a set-based solution that will run quite a bit faster than your cursor solution. Take a look at the first link in my signature line below for a couple of tips on how to post such a question to get tested solutions faster.

--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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

Jeff Moden (9/3/2009)
...
.enots ot nrut t'nod I os rorrim a hguorht siht ta gnikool yllautca m'I ...denrawerof neeb d'I 😉

Uhm, OK, I'm going to try to counterspell Zantana here, (rattle rattle), ... 18! Sweet! 😀

[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]

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 9

Jeff... my wife was just reading over my shoulder at your mirror text... she said (and I quote)....

OMG... WTF... How do you do that without having a seizure?

Then she stormed out before I could tell her about the REVERSE function. Of course, she may have been talking about me reading it out loud from right to left.

It's good to be in SSC. 😀

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1003901 More actions · Answer 10

REVERSE? Nah... just wicked dyslexic this time of day. 😛

--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)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

Jeff Moden (9/3/2009)
CirquedeSQLeil (9/3/2009)
Paul White (9/3/2009)
Just for completeness, this is also valid:
-- Just to simulate some condition or other
declare @v-2 int;
set @v-2 = 2;
-- Dear Lord, why am I using a cursor here?
-- (probably an updatable, global, dynamic cursor at that!)
declare mycur cursor for
The real question is "What is a set-based query?"
Jeff must have seen it by now and had a heart-attack 😀
BTW - best laugh of the day
BWAA-HAA!!! "Oh my! I have a feeling we're not in Kansas anymore, Toto." And, the Munchins know a whole bunch of SQL curses (or is that "cursors"), too! 😛 (Going back to reading with mirror)

Somebody is a little jacked up on caffeine and fruit-loops, me doth thinks.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

Bob Hovious (9/3/2009)
Sudhanva,
Please do not think for a moment that we are making fun of you personally. Obviously, those of us who volunteer to help others have a prejudice against the use of cursors. This is because, as a general rule, they run slowly and are far too often used instead of a set based solution. (They are also evil and should be destroyed, but I am digressing.)
Regular volunteers all come "know" each other, at least from our postings, and we sometimes joke around with each other in these threads. Rest assured, the joking is NOT at your expense.

Thanks Bob for bringing us all back down to earth.

And yes, Bob is correct here - please don't see these posts as personal - they are meant to poke a little fun back and forth amongst others we feel we might know through the forum.

Somebody break that mirror of Jeff's too. :hehe:

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

David Burrows SSC Guru Points: 65138 More actions · Answer 13

Jeff Moden (9/3/2009)
.enots ot nrut t'nod I os rorrim a hguorht siht ta gnikool yllautca m'I ...denrawerof neeb d'I 😉

:crazy:

iday eenbay orewarnedfay... imay actuallyay ookinglay atay isthay oughthray a irrormay osay iay ontday urnyay otay onestay

😛

Far away is close at hand in the images of elsewhere.
Anon.

Jeff Moden SSC Guru Points: 1003901 More actions · Answer 14

David Burrows (9/4/2009)
Jeff Moden (9/3/2009)
.enots ot nrut t'nod I os rorrim a hguorht siht ta gnikool yllautca m'I ...denrawerof neeb d'I 😉
:crazy:
iday eenbay orewarnedfay... imay actuallyay ookinglay atay isthay oughthray a irrormay osay iay ontday urnyay otay onestay
😛

Dobo yobou spobeek obEnglobish obas wobell? 😉

--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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 15

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". 😛

[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]