SQL Server Delete on read

Question

Is there an easy way in SQLServer touse data as READ_ONCE? What I mean is, can I set it to delete a row after it has selected it?

Answer 1

You could do this easily if the data is accessed through a stored procedure. You can select the data into a temp table, delete the data and return the temp. All wrapped in a transaction of course.

Answer 2

Off the top of my head, the only way I can think of would be to restrict all logins to prohiibit any Select access, and only allow access through a stored procedure "FetchMyWhateverData" and then delete the rows as second SQL statement inside the stored proc.

CreateProcedure FetchMyWhateverData
@MyEntityId Integer,
As
Set NoCount On

    Select * From TableName 
    Where Id = @MyEntityId 

    Delete TableName 
    Where Id = @MyEntityId 

    Return 0

-- and adding in the other appropriate infrastructure code of course.

Answer 3

If you read it with DELETE ... OUTPUT .... This is how queues work.