September 3, 2009 at 6:14 am
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
September 3, 2009 at 6:24 am
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
September 3, 2009 at 6:34 am
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
September 3, 2009 at 6:49 am
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
September 3, 2009 at 8:01 am
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?
September 3, 2009 at 8:06 am
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
September 3, 2009 at 8:21 am
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.
September 3, 2009 at 8:36 am
sudhanva (9/3/2009)
Dave Ballantyne (9/3/2009)
no , but you can wrap the declare within an IFWhy 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
September 3, 2009 at 9:38 am
Just for completeness, this is also valid:
-- Just to simulate some condition or other
declare @v int;
set @v = 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 = 1
union all
select 2 from sys.tables where @v 1 or @v is null;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 3, 2009 at 9:46 am
-- 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
September 3, 2009 at 9:57 am
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
September 3, 2009 at 9:58 am
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]
September 3, 2009 at 10:51 am
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
September 3, 2009 at 11:29 am
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
September 3, 2009 at 12:08 pm
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.
Viewing 15 posts - 1 through 15 (of 40 total)
You must be logged in to reply to this topic. Login to reply