Stored procedure: delete rows from multiple tables with output for every row

Question

I want to delete rows from 3 related tables in a stored procedure entering the ID of Table1 (t1ID).

Table1       Table2       Table3
-------      -------      -------
t1ID         t2ID         t3ID
name         t1ID         t2ID
             blobpath1    blobpath2 

I need to get the values of blobpath1 and blobpath2 of every deleted row as output from the procedure, because I have to delete blob storage outside of the database.

Usually I would do:

Delete From [dbo].[Table3]  Where t2ID IN
(Select t2ID FROM [dbo].[Table2] Where [t1ID]= @t1ID)
Delete From [dbo].[Table2] Where [t1ID]= @t1ID
Delete From [dbo].[Table1] Where [t1ID]= @t1ID

How can I output the blobpaths?

Answer 1

You can do it fairly simply using the OUTPUT clause;

BEGIN TRANSACTION;

DECLARE @blobpaths TABLE ( blobpath VARCHAR(32) );

DELETE t3 OUTPUT deleted.blobpath2 INTO @blobpaths
FROM table3 t3 
JOIN table2 t2 ON t2.t2id = t3.t2id
JOIN table1 t1 ON t1.t1id = t2.t1id 
WHERE t1.t1id = @t1ID;

DELETE t2 OUTPUT deleted.blobpath1 INTO @blobpaths
FROM table2 t2 
JOIN table1 t1 ON t1.t1id = t2.t1id
WHERE t1.t1id = @t1ID;

DELETE FROM table1 WHERE t1id = @t1ID;

SELECT blobpath FROM @blobpaths;

COMMIT TRANSACTION;

An SQLfiddle to test with.