Specifies that the RECEIVE statement waits for a message to arrive on the queue, if no messages are currently present.

TOP( n )

Specifies the maximum number of messages to be returned. If this clause is not specified, all messages are returned that meet the statement criteria.

*

Specifies that the result set contains all columns in the queue.

column_name

The name of a column to include in the result set.

expression

A column name, constant, function, or any combination of column names, constants, and functions connected by an operator.

column_alias

An alternative name to replace the column name in the result set.

FROM

Specifies the queue that contains the messages to retrieve.

database_name

The name of the database that contains the queue to receive messages from. When no database name is provided, defaults to the current database.

schema_name

The name of the schema that owns the queue to receive messages from. When no schema name is provided, defaults to the default schema for the current user.

queue_name

The name of the queue to receive messages from.

INTO table_variable

Specifies the table variable that RECEIVE places the messages into. The table variable must have the same number of columns as are in the messages. The data type of each column in the table variable must be implicitly convertible to the data type of the corresponding column in the messages. If INTO is not specified, the messages are returned as a result set.

WHERE

Specifies the conversation or conversation group for the received messages. If omitted, returns messages from the next available conversation group.

conversation_handle = conversation_handle

Specifies the conversation for received messages. The conversation handle provided must be a uniqueidentifer, or a type that is convertible to uniqueidentifier.

conversation_group_id = conversation_group_id

Specifies the conversation group for received messages. The conversation group ID that is provided must be a uniqueidentifier, or a type convertible to uniqueidentifier.

TIMEOUT timeout

Specifies the amount of time, in milliseconds, for the statement to wait for a message. This clause can only be used with the WAITFOR clause. If this clause is not specified, or the time-out is -1, the wait time is unlimited. If the time-out expires, RECEIVE returns an empty result set.

If the RECEIVE statement is not the first statement in a batch or stored procedure, the preceding statement must be ended with a semi-colon (;).

The RECEIVE statement reads messages from a queue and returns a result set. The result set consists of zero or more rows, each of which contains one message. If the INTO clause is not used, and column_specifier does not assign the values to local variables, the statement returns a result set to the calling program.

The messages that are returned by the RECEIVE statement can be of different message types. Applications can use the message_type_name column to route each message to code that handles the associated message type. There are two classes of message types:

Application-defined message types that were created by using the CREATE MESSAGE TYPE statement. The set of application-defined message types that are allowed in a conversation are defined by the Service Broker contract that is specified for the conversation.

Service Broker system messages that return status or error information.

The RECEIVE statement removes received messages from the queue unless the queue specifies message retention. When the RETENTION setting for the queue is ON, the RECEIVE statement updates the status column to 0 and leaves the messages in the queue. When a transaction that contains a RECEIVE statement rolls back, all changes to the queue in the transaction are also rolled back, returning messages to the queue.

All messages that are returned by a RECEIVE statement belong the same conversation group. The RECEIVE statement locks the conversation group for the messages that are returned until the transaction that contains the statement finishes. A RECEIVE statement returns messages that have a status of 1. The result set returned by a RECEIVE statement is implicitly ordered:

If messages from multiple conversations meet the WHERE clause conditions, the RECEIVE statement returns all messages from one conversation before it returns messages for any other conversation. The conversations are processed in descending priority level order.

For a given conversation, a RECEIVE statement returns messages in ascending message_sequence_number order.

The WHERE clause of the RECEIVE statement can only contain one search condition that uses either conversation_handle or conversation_group_id. The search condition cannot contain one or more of the other columns in the queue. The conversation_handle or conversation_group_id cannot be an expression. The set of messages that is returned depends on the conditions that are specified in the WHERE clause:

If conversation_handle is specified, RECEIVE returns all messages from the specified conversation that are available in the queue.

If conversation_group_id is specified, RECEIVE returns all messages that are available in the queue from any conversation that is a member of the specified conversation group.

If there is no WHERE clause, RECEIVE determines which conversation group:

Has one or more messages in the queue.

Has not been locked by another RECEIVE statement.

Has the highest priority level of all the conversation groups that meet these criteria.

RECEIVE then returns all messages available in the queue from any conversation that is a member of the selected conversation group.

If the conversation handle or conversation group identifier specified in the WHERE clause does not exist, or is not associated with the specified queue, the RECEIVE statement returns an error.

If the queue specified in the RECEIVE statement has the queue status set to OFF, the statement fails with a Transact-SQL error.

When the WAITFOR clause is specified, the statement waits for the specified time out, or until a result set is available. If the queue is dropped or the status of the queue is set to OFF while the statement is waiting, the statement immediately returns an error. If the RECEIVE statement specifies a conversation group or conversation handle and the service for that conversation is dropped or moved to another queue, the RECEIVE statement reports a Transact-SQL error.

RECEIVE is not valid in a user-defined function.

The RECEIVE statement has no priority starvation prevention. If a single RECEIVE statement locks a conversation group and retrieves a lot of messages from low priority conversations, no messages can be received from high priority conversations in the group. To prevent this, when you are retrieving messages from low priority conversations, use the TOP clause to limit the number of messages retrieved by each RECEIVE statement.

B. Receiving specified columns for all messages in a conversation group

The following example receives all available messages for the next available conversation group from the ExpenseQueue queue. The statement returns the messages as a result set that contains the columns conversation_handle, message_type_name, and message_body.

G. Receiving messages and waiting indefinitely

The following example receives all available messages for the next available conversation group in the ExpenseQueue queue. The statement waits until at least one message becomes available then returns a result set that contains all message columns.

H. Receiving messages and waiting for a specified interval

The following example receives all available messages for the next available conversation group in the ExpenseQueue queue. The statement waits for 60 seconds or until at least one message becomes available, whichever occurs first. The statement returns a result set that contains all message columns if at least one message is available. Otherwise, the statement returns an empty result set.

I. Receiving messages, modifying the type of a column

The following example receives all available messages for the next available conversation group in the ExpenseQueue queue. When the message type states that the message contains an XML document, the statement converts the message body to XML.

J. Receiving a message, extracting data from the message body, retrieving conversation state

The following example receives the next available message for the next available conversation group in the ExpenseQueue queue. When the message is of type //Adventure-Works.com/Expenses/SubmitExpense, the statement extracts the employee ID and a list of items from the message body. The statement also retrieves state for the conversation from the ConversationState table.