IF condition in CURSOR

Question

Post reply

IF condition in CURSOR

sudhanva

Ten Centuries

Points: 1343
More actions
September 3, 2009 at 6:14 am

#141345

Hi,
Can we have IF condition in cursor, after FOR
something like below:
DECLARE vendor_cursor CURSOR FOR
IF 1=1 --can we add IF condition here?
BEGIN
SELECT VendorID, Name
FROM Purchasing.Vendor
WHERE PreferredVendorStatus = 1
ORDER BY VendorID
END
ELSE
BEGIN
SELECT VendorID, Name
FROM Purchasing.Vendor
WHERE PreferredVendorStatus = 2
ORDER BY VendorID
END

Viewing 15 posts - 1 through 15 (of 40 total)

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

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

no , but you can wrap the declare within an IF

if(1=1) begin

declare mycur cursor for select 1 from sys.tables

end else begin

declare mycur cursor for select 2 from sys.tables

end

Clear Sky SQL
My Blog[/url]

Lowell SSC Guru Points: 323507 More actions · Answer 2

you could also use a CASE inside the cursor for the evaluation:

i assume your 1=1 would actually be a real test condition;

SELECT VendorID, Name

FROM Purchasing.Vendor

WHERE PreferredVendorStatus =

CASE

WHEN MyCondition = 1

THEN 1

WHEN MyCondition = 2

THEN 2

ELSE 3

END

ORDER BY VendorID

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

The nice thing about Lowell's CASE solution is that it could lead you to get away from the CURSOR entirely, which is always a happy thing.

__________________________________________________

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

sudhanva Ten Centuries Points: 1343 More actions · Answer 4

Dave Ballantyne (9/3/2009)
no , but you can wrap the declare within an IF

Why No?

Can anybody provide some links/info on this?

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

Because they didn't write the language to allow it that way.

__________________________________________________

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

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

sudhanva (9/3/2009)
Can anybody provide some links/info on this?

Books online is the best resource for checking syntax, though im not sure what you are expecting to find.

Clear Sky SQL
My Blog[/url]

GSquared SSC Guru Points: 260824 More actions · Answer 7

sudhanva (9/3/2009)
Dave Ballantyne (9/3/2009)
no , but you can wrap the declare within an IF
Why No?
Can anybody provide some links/info on this?

This link provides the full syntax for declaring a cursor: http://msdn.microsoft.com/en-us/library/ms180169.aspx

You may notice that it does not have an "IF" option between Declare and For.

I highly recommend learning how to use online search tools like Google/Bing. I also recommend becoming familiar with MSDN, since it has the full syntax documentation for T-SQL. Those two tools will answer this kind of question very easily. Online search skills are necessary to any sort of IT work, regardless of the specific field. Anything involving Microsoft tools (Windows, .NET, T-SQL, Office, etc.) will require familiarity with MSDN.

Books Online, which can be installed locally on your computer when you install SQL Management Studio, contains a copy of the SQL Server portion of MSDN, and can thus be used as well, with the advantage of being available if you don't have an internet connection, and the disadvantage that you have to download any updates to it or it risks getting outdated.

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

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

select 1 from sys.tables where @v-2 = 1

union all

select 2 from sys.tables where @v-2 1 or @v-2 is null;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

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

__________________________________________________

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

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

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

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

Paul White (9/3/2009)
... -- Dear Lord, why am I using a cursor here? -- (probably an updatable, global, dynamic cursor at that!) ...

Definitely my favorite comment of the day! 🙂

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

I hope Jeff never sees this thread...

You mean Cardinal Moden of the SQL Inquisition? :w00t::w00t::w00t:

__________________________________________________

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

Grant Fritchey SSC Guru Points: 398715 More actions · Answer 13

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.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

Bob Hovious (9/3/2009)
I hope Jeff never sees this thread...
You mean Cardinal Moden of the SQL Inquisition? :w00t::w00t::w00t:

Hey Bob. another awesome avatar you have there this time.