Post reply

Another cursor conversion...

  • July 13, 2005 at 2:13 pm

    #88083

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

     

     

     

  • July 13, 2005 at 2:17 pm

    #574121

    This is one way to do it... you can also use delete from join syntaxe. But it yields the same execution plan.

  • July 13, 2005 at 2:21 pm

    #574123

    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=@corrid

    I'm never sure about joins...

     

  • July 13, 2005 at 2:27 pm

    #574128

    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

  • July 13, 2005 at 2:29 pm

    #574130

    I think he got that point earlier .

  • July 13, 2005 at 2:31 pm

    #574131

    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

  • July 13, 2005 at 2:40 pm

    #574139

    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.

     

  • July 13, 2005 at 2:43 pm

    #574141

    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.

  • July 13, 2005 at 2:44 pm

    #574143

    Dam! you keep typing fast

     


    * Noel

  • July 13, 2005 at 2:48 pm

    #574147

    I won't be slowing down for you honey . Did you recheck the load testing thread we started yesterday?

  • July 13, 2005 at 3:06 pm

    #574155

    Nice Job. and i think it was a good thread, see my post there 

     


    * Noel

  • July 13, 2005 at 3:15 pm

    #574161

    I did.. already re-replied there .

  • July 14, 2005 at 5:55 am

    #574313

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

     

  • July 14, 2005 at 6:29 am

    #574326

    ... where id = @Param... or you will whip out the while thing .

  • July 14, 2005 at 6:33 am

    #574327

    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=@corrid

    Right?

     

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

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