SQL Server Delete on read

Is there a开发者_如何学Pythonn 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?

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.

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

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.