user584018 user584018 - 2 months ago 10
SQL Question

How to return result set along with delete

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

Answer

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.

Comments