Efficient way to delete multiple rows in sp with o

Question

The below query returns me two rows:

SELECT 
DocList.DocListId, RegistrationDocList.RegistrationDocListId, Registration.RegistrationId 
FROM 
DocList INNER JOIN
RegistrationDocList ON DocList.DocListId = RegistrationDocList.DocListId INNER JOIN
Registration ON RegistrationDocList.RegistrationId = Registration.RegistrationId
WHERE (DocList.DocListId = 547)

Results:

--------------------------------------------------------
DocListId   RegistrationDocListId    RegistrationId
-------------------------------------------------------
547              097                     115
547              098                     116

UPDATE:

as you can see its using three tables, so i want to delete the rows from:

DocList where doclistid = 547
RegistrationDocList.RegistrationId  where RegistrationId in (097,098)
Registration.RegistrationId  where RegistrationId in (115,116)

Answer 1

You need to cache the values to delete on the far side of the link table, delete the link records, and then delete the linked records:

DECLARE @reg_to_delete TABLE (id int)

INSERT INTO @reg_to_delete (id)
SELECT RegistrationID FROM RegistrationDocList WHERE DocListID = 547

DELETE FROM RegistrationDocList WHERE DocListID = 547

DELETE FROM RegistrationList WHERE RegistrationID IN (SELECT id FROM @reg_to_delete)

DELETE FROM DocList WHERE DocListID = 547

Answer 2

Maybe I missed your point. I hope this code will help you:

DECLARE @Result int

BEGIN TRAN
    BEGIN TRY
        DELETE DocList where doclistid = 547
        DELETE RegistrationDocListwhere RegistrationId in (097,098)
        DELETE Registration where RegistrationId in (115,116)

        /* code was done properly => mark flag as OK */
        SET @Result = 0
    END TRY
    BEGIN CATCH
        /* some error occured => mark flag as ERROR */
        SET @Result = -1
    END CATCH

IF @Result = 0 BEGIN
    /* all is fine, transaction can be commited */
    COMMIT TRAN
END
ELSE BEGIN
    /* something is wrong, transaction must be rolled back */
    ROLLBACK TRAN
END