Is it possible to use a case statement inside a cursor?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Is it possible to use a case statement inside...

Is it possible to use a case statement inside a cursor?

Rate Topic

Display Mode

Topic Options

Author

Message

HildaJ

Posted Tuesday, February 05, 2013 4:04 PM

SSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 7:38 AM
Points: 89, Visits: 261

I have to update a group of records but I need to make some comparison before actually making the update. I'm using a cursor to loop through my records, depending on my rows data I can either update or delete the row. Inside this loop I'm using a case statement but it keeps giving me a syntas error I'm not sure what the syntax error is. Here's my code:

DECLARE SelectRecToUpdate CURSOR READ_ONLY FOR
SELECT * FROM Table1
WHERE [CompanyID] = @MergeNum
OPEN SelectRecToUpdate

FETCH NEXT FROM SelectRecToUpdate INTO @CompanyID, @RoleID, @ContactID, @DateEffBeg
WHILE @@Fetch_Status = 0
BEGIN

-- for each row when replaced the current CompanyID, check if PK combination already exist
SELECT @GetRowCount = COUNT(*) FROM Table2
WHERE @CompanyID = @MergeTo

CASE
WHEN @GetRowcount=0 THEN
-- insert
ELSE
-- update
END

FETCH NEXT FROM SelectRecToUpdate INTO @CompanyID, @RoleID, @ContactID, @DateEffBeg;
END

CLOSE SelectRecToUpdate
DEALLOCATE SelectRecToUpdate
GO

Post #1416142

SQLRNNR

Posted Tuesday, February 05, 2013 4:09 PM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:46 PM
Points: 18,732, Visits: 12,329

You can't use CASE in that way. What you want to be using is IF blocks.

IF @GetRowcount=0 
Begin
-- insert
END
IF @GetRowcount<>0 
BEGIN
-- update
END

But this really looks like a prime setup for a set based activity rather than a cursor. Even with the Conditions (of the CASE), you can do this via set based logic and improve the performance.

Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #1416146

dwain.c

Posted Tuesday, February 05, 2013 6:11 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:42 AM
Points: 2,338, Visits: 3,158

SQLRNNR (2/5/2013)

You can't use CASE in that way. What you want to be using is IF blocks.

IF @GetRowcount=0 
Begin
-- insert
END
IF @GetRowcount<>0 
BEGIN
-- update
END

But this really looks like a prime setup for a set based activity rather than a cursor. Even with the Conditions (of the CASE), you can do this via set based logic and improve the performance.

+1 to that!

The correct question to be asking is "why am I using a CURSOR for this?"

BTW Hilda - Nice cat avatar. Looks liky my Egyptian Mau.

No loops! No CURSORs! No RBAR! Hoo-uh!

INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!

Post #1416193

HildaJ

Posted Wednesday, February 06, 2013 7:03 AM

SSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 7:38 AM
Points: 89, Visits: 261

I'm very new to TSQL so my apologies for my lack of experience. Really working on it : ) So instead of using a cursor what would be another option? Do you have any pages and references that I can look at to get examples? Also, what is based activity? I don't think I'm familiar with it.

thank you.

Post #1416493

SQLRNNR

Posted Wednesday, February 06, 2013 8:57 AM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:46 PM
Points: 18,732, Visits: 12,329

set-based means that you don't use a looping mechanism such as a cursor. Rather than updating 1 record at a time, you update the entire group (set) that meets the update requirements.

Post #1416566

dwain.c

Posted Wednesday, February 06, 2013 6:34 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:42 AM
Points: 2,338, Visits: 3,158

HildaJ (2/6/2013)

Hilda - The first thing that you should try to do is understand the term RBAR (in my signature), so that you can then learn to avoid it. You can Google that and find loads of info.

Since Jeff Moden coined the term, a good place to start would be any of his articles that mention RBAR in the title. Here is his guest columnist page:
http://www.sqlservercentral.com/Authors/Articles/Jeff_Moden/80567/

Don't get frustrated if some of these may be too advanced for your current technical level. File them in long term memory for future retrieval when needed (and believe me that will come in handy).

The best thing you can do for yourself is to forget that you've ever heard of CURSORs and start your thinking off by writing your SQL script without using them. 95-98% of the time there will be a set-based solution for a normal SQL query you'd be running against an OLTP (or OLAP) database.

Post #1416778

demonfox

Posted Thursday, February 07, 2013 1:41 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 6:46 PM
Points: 1,074, Visits: 1,076

HildaJ (2/6/2013)

From the code I can gather, that you want to insert or update the table 2 based on the table1 data;

I think Merge should be a good approach..

check out this link :

http://technet.microsoft.com/en-us/library/bb510625.aspx

this should help ..

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one Ermm

Post #1416873

HildaJ

Posted Friday, February 08, 2013 7:53 AM

SSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 7:38 AM
Points: 89, Visits: 261

Oh my, when I think of looping through a record set I automatically think about cursors that was the first approach I learned for looping. It's going to be difficult to start considering cursors. Grrrrrh!! T

Thank you so much for the responses. I will definitely look into all your solutions and I will review the material. I really like this forum, it's full of resources and people that are so knowledgable.

: )

Post #1417713

« Prev Topic | Next Topic »

Permissions