Rules for Consuming Rows From a Queue
The following rules and recommendations apply to consuming rows from a queue table
(see SQL Data Manipulation Language for a complete description of the rules and limits for consuming queue table rows).
To consume rows from a queue table, specify the AND CONSUME TOP 1 keywords in your
SELECT request. The AND CONSUME keywords indicate that the request is a consume mode
request, while TOP 1 indicates that the oldest row from the queue table is to be retrieved.
The following example consumes a row from a queue table named shopping_cart.
SELECT AND CONSUME TOP 1 *
You can also consume rows from stored procedures and embedded SQL applications that
use the INTO clause to assign the values from a row to host or local variables.
You should place any action taken based on consuming a row from a queue table in the
same transaction as the consume mode SELECT operation on that same queue table. This
ensures that both the row consumption and the action taken on that queue table are
committed together, so no row or action for that queue table is lost.
If no action is to be taken, then you should isolate any SELECT AND CONSUME request
as the only request in a transaction (see SQL Request and Transaction Processing).
A multistatement request should contain only 1 consume mode SELECT request.
A trigger can call a stored procedure that consumes a row from a queue table.