How to return result set along with delete

Question

I am trying to get the record from table QUEUE as well as delete the same record.

CREATE TABLE DBO.QUEUE 
( 
     QUEUEID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, 
     SOMEACTION VARCHAR(100)
)

Through this query using updlock & readpast I am able to delete the record what already processed, but I need also that record as a result, how to do this?

I need both QUEUEID and SOMEACTION column in the result set.

BEGIN TRAN TRAN1  

SELECT TOP 1 @queueid = QUEUEID  
FROM DBO.QUEUE WITH (updlock, readpast)  

PRINT 'processing queueid # ' + CAST(@queueid AS VARCHAR)  

DELETE FROM DBO.QUEUE  
WHERE QUEUEID = @queueid 

COMMIT

What version of SQL are you using? A transaction seems appropriate here. — Tim Biegeleisen, Sep 11 '16 at 05:17
Did you read the [manual](https://msdn.microsoft.com/en-us/library/ms189835.aspx#CaptureResults)? `DELETE FROM DBO.QUEUE OUTPUT DELETED.QUEUEID, DELETED.SOMEACTION WHERE QUEUEID = @queueid` — Andreas, Sep 11 '16 at 05:28
So if you read the manual and already know about the `OUTPUT` clause, why are you asking how to do it? — Andreas, Sep 11 '16 at 05:34

score 3 · Accepted Answer · answered Sep 11 '16 at 06:48

3

As you and @Andreas already mentioned - use the OUTPUT clause with your DELETE statement:

DELETE FROM DBO.QUEUE  
OUTPUT deleted.QueueId, deleted.SomeAction
WHERE QUEUEID = @queueid

This will return (as a result set) the values that have been deleted.

answered Sep 11 '16 at 06:48

marc_s

732,580
175
1,330
1,459

How to return result set along with delete

1 Answers1