Another cursor conversion...
-
... so I found this code in a stored procedure that was running longer than we'd like:
DECLARE partyCursor CURSOR FOR
SELECT PARTY_ID FROM PARTY WHERE CORRESPONDENCE_ID = @corrid
--Delete Parties
OPEN partyCursor
FETCH partyCursor INTO @partyId
WHILE (@@FETCH_STATUS=0) BEGIN
--Delete comments associated with this party
DELETE FROM PARTY_COMMENT WHERE PARTY_ID = @partyId
--Delete phones associated with this party
DELETE FROM PHONE WHERE PARTY_ID = @partyId
FETCH partyCursor INTO @partyId
END
CLOSE partyCursor
DEALLOCATE partyCursor
How to rewrite this in Set-based code? I thought maybe:
- DELETE FROM PARTY_COMMENT WHERE PARTY_ID IN
- (SELECT PARTY_ID FROM PARTY WHERE CORRESPONDENCE_ID = @corrid)
- DELETE FROM PHONE WHERE PARTY_ID IN
- (SELECT PARTY_ID FROM PARTY WHERE CORRESPONDENCE_ID = @corrid)
But I'm not sure and I don't have a good way to test it.
a. Will this work?
2. Is there a better way? (Subjective, I know, but I'm curious...)
III. Have I completely missed the point??
-
This is one way to do it... you can also use delete from join syntaxe. But it yields the same execution plan.
-
- You mean like:
- DELETE FROM PARTY_COMMENT C JOIN PARTY P ON C.PARTY_ID=P.PARTY_ID
- WHERE <A href="mailto
.CORRESPONDENCE_ID=@corrid">P.CORRESPONDENCE_ID=@corridI'm never sure about joins...
-
True, Delete syntax is ugly whe using From
Delete C
From
PARTY_COMMENT C JOIN PARTY P ON C.PARTY_ID=P.PARTY_ID
- <A href="mailto
.CORRESPONDENCE_ID=@corrid"> and then another one for Phone
And this two will FLIGHT compared to the loop
[EDIT:] By the way you should pass a list of all the IDs that are to be removed to a where clause
* Noel - <A href="mailto
-
I think he got that point earlier
. -
True, Just making sure that he is aware of that he should pass the list or use one of those tableFunctions that you like to post
* Noel -
Delete C
From
PARTY_COMMENT C JOIN PARTY P ON C.PARTY_ID=P.PARTY_ID
Huh?
I don't follow this one at all. How does this relate them back to the @corrid parameter passed into the SP (there's only one by the way, the idea is that each Correspondence has a Party record and each Party record may have multiple Comments and Phones. Party_Comment and Phone relate to Party via Party_ID, Party relates to Correspondence via Correspondence_ID.
By the way you should pass a list of all the IDs that are to be removed to a where clause
You really lost me there. List of ID's? If you mean Correspondence_IDs there's only one.
-
This is the missing part of Noeld's solution :
WHERE P.CORRESPONDENCE_ID=@corrid
What he meant was to pass multiple ids at once instead of calling this proc multiple times.
-
Dam! you keep typing fast
* Noel -
I won't be slowing down for you honey
. Did you recheck the load testing thread we started yesterday? -
Nice Job. and i think it was a good thread, see my post there
* Noel -
I did.. already re-replied there
. -
Ah.. I get it. There's only one correspondence to delete. There may be multiple party entries, but they're deleted with
DELETE FROM PARTY WHERE CORRESPONDENCE_ID=@corrid
It's the PARTY_COMMENT and PHONE records that were trickier because they relate to the PARTYs, not the original CORRESPONDENCE.
So you can say:
- Delete C
- From
- PARTY_COMMENT C JOIN PARTY P ON C.PARTY_ID=P.PARTY_ID
- <A href="http://www.sqlservercentral.com/forums/mailto
.CORRESPONDENCE_ID=@corrid">
-
... where id = @Param... or you will whip out the while thing
. -
- I'd swear I'd coded the whole thing:
- Delete C
- From
- PARTY_COMMENT C JOIN PARTY P ON C.PARTY_ID=P.PARTY_ID
- WHERE <A href="mailto
.CORRESPONDENCE_ID=@corrid">P.CORRESPONDENCE_ID=@corridRight?
- I'd swear I'd coded the whole thing:
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply