February 5, 2013 at 4:04 pm
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
February 5, 2013 at 4:09 pm
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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 5, 2013 at 6:11 pm
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.
My thought question: Have you ever been told that your query runs too fast?
My advice:
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?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 6, 2013 at 7:03 am
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.
February 6, 2013 at 8:57 am
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.
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
February 6, 2013 at 6:34 pm
HildaJ (2/6/2013)
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.
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.
My thought question: Have you ever been told that your query runs too fast?
My advice:
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?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 7, 2013 at 1:41 am
HildaJ (2/6/2013)
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.
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:
February 8, 2013 at 7:53 am
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.
: )
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply