WAITFOR

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.

Important:
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. For more information, see Broker System Messages.
  

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.

To receive a message, the current user must have RECEIVE permission on the queue.

The following table lists the columns in a queue:

A. Receiving all columns for all messages in a conversation group

RECEIVE * FROM ExpenseQueue ;

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

RECEIVE conversation_handle, message_type_name, message_body
FROM ExpenseQueue ;

C. Receiving the first available message in the queue

RECEIVE TOP (1) * FROM ExpenseQueue ;

D. Receiving all messages for a specified conversation

DECLARE @conversation_handle UNIQUEIDENTIFIER ;

SET @conversation_handle = <retrieve conversation from database> ;

RECEIVE *
FROM ExpenseQueue
WHERE conversation_handle = @conversation_handle ;

E. Receiving messages for a specified conversation group

DECLARE @conversation_group_id UNIQUEIDENTIFIER ;

SET @conversation_group_id = 
    <retrieve conversation group ID from database> ;

RECEIVE *
FROM ExpenseQueue
WHERE conversation_group_id = @conversation_group_id ;

F. Receiving into a table variable

DECLARE @conversation_group_id UNIQUEIDENTIFIER ;

DECLARE @procTable TABLE(
     service_instance_id UNIQUEIDENTIFIER,
     handle UNIQUEIDENTIFIER,
     message_sequence_number BIGINT,
     service_name NVARCHAR(512),
     service_contract_name NVARCHAR(256),
     message_type_name NVARCHAR(256),
     validation NCHAR,
     message_body VARBINARY(MAX)) ;

SET @conversation_group_id = <retrieve conversation group ID from database> ;

RECEIVE TOP (1)
    conversation_group_id,
    conversation_handle,
    message_sequence_number,
    service_name,
    service_contract_name,
    message_type_name,
    validation,
    message_body
FROM ExpenseQueue
INTO @procTable
WHERE conversation_group_id = @conversation_group_id ;

G. Receiving messages and waiting indefinitely

WAITFOR (
    RECEIVE *
    FROM ExpenseQueue) ;

H. Receiving messages and waiting for a specified interval

WAITFOR (
    RECEIVE *
    FROM ExpenseQueue ),
TIMEOUT 60000 ;

I. Receiving messages, modifying the type of a column

WAITFOR (
    RECEIVE message_type_name,
        CASE
            WHEN validation = 'X' THEN CAST(message_body as XML)
            ELSE NULL
         END AS message_body 
         FROM ExpenseQueue ),
TIMEOUT 60000 ;

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

WAITFOR(
    RECEIVE 
    TOP(1)
      message_type_name,
      COALESCE(
           (SELECT TOP(1) ConversationState
            FROM CurrentConversations AS cc
            WHERE cc.ConversationHandle = conversation_handle),
           'NEW')
      AS ConversationState,
      COALESCE(
          (SELECT TOP(1) ErrorCount
           FROM CurrentConversations AS cc
           WHERE cc.ConversationHandle = conversation_handle), 
           0)
      AS ConversationErrors,
      CASE WHEN message_type_name = N'//Adventure-Works.com/Expenses/SubmitExpense'
          THEN CAST(message_body AS XML).value(
                'declare namespace rpt = "http://Adventure-Works.com/schemas/expenseReport"
                   (/rpt:ExpenseReport/rpt:EmployeeID)[1]', 'nvarchar(20)')
         ELSE NULL
      END AS EmployeeID,
      CASE WHEN message_type_name = N'//Adventure-Works.com/Expenses/SubmitExpense'
          THEN CAST(message_body AS XML).query(
                'declare namespace rpt = "http://Adventure-Works.com/schemas/expenseReport" 
                     /rpt:ExpenseReport/rpt:ItemDetail')
          ELSE NULL
      END AS ItemList
    FROM ExpenseQueue 
), TIMEOUT 60000 ;

Reference

Other Resources

Help and Information