Logic for Stored Procedure(s)

I have a table containing a 'queue' of rows waiting to be processed by my application.

Is it possible to call a single stored procedure that selects a row, returns the data and then deletes the row ?

If not, what is the best logic for doing it with two stored procedures ?

The table contains a unique ID, DateTime and nVarChar colums and I could easily add a 'flag' if required.

Any suggestions appreciated.

Steve.A single stored prodedure can have many lines of code, so sure, what you are asking is possible.

I'd do it like this:

SET NOCOUNT ON
BEGIN TRANSACTION
 SELECT TOP 1 @.myID = uniqueID FROM myTable ORDER BY datetime DESC
 SELECT someFields FROM myTable WHERE uniqueID = @.myID
 DELETE FROM myTable WHERE uniqueID = @.myID
COMMIT

I'd also put some error checking code in there and perform a ROLLBACK if there's an error.

Terri|||I might create a flag on myTable indicating that the record has processed, move the tran to the client, and update the flag once the process has sucessfully completed.

If the server commits the tran before the client finishes processing and the client error you will have no "easy" way of recovering a deleted record.|||Thanks guys - that helps a lot.

Steve.