-- Tested with SQL Server 2005 9.00.852
-- Service Broker Service Stored Procedure Handler
-- Code that handles the messages in the Service Broker queue:
USE DotNetFun;
-- Settings
DECLARE @TimeoutMS INT
SET @TimeoutMS = 500
DECLARE @DialogHandle UNIQUEIDENTIFIER, 
	@ConversationGroupHandle UNIQUEIDENTIFIER
DECLARE @InProcTable TABLE 
(
     status TINYINT,  
	 service_instance_id UNIQUEIDENTIFIER,
     conversation_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), 
	 conversation_group_id UNIQUEIDENTIFIER
);
-- Iterate through each conversation group:
-- Get first available group of messages:
WAITFOR (
	GET CONVERSATION GROUP @ConversationGroupHandle
		FROM DotNetFunQueue
), TIMEOUT @TimeoutMS;
WHILE (@ConversationGroupHandle IS NOT NULL)
BEGIN
	-- Retrieve the message and wait 1 sec. for it to arrive:
	WAITFOR
	(
		-- Insert the contents of the message queue 
		-- table into an InProc table:
		RECEIVE  
			status, 
			conversation_group_id,
			conversation_handle,
			message_sequence_number,
			service_name,
			service_contract_name,
			message_type_name,
			validation,
			message_body, 
			conversation_group_id
		FROM DotNetFunQueue 
		INTO @InProcTable 
		WHERE conversation_group_id = @ConversationGroupHandle
	), TIMEOUT @TimeoutMS; 
	-- Note: the TIMEOUT feature ONLY works with Service Broker messages.
	-- Get the next available conversation group:
	BEGIN TRY
		WAITFOR (
			GET CONVERSATION GROUP @ConversationGroupHandle
			FROM DotNetFunQueue
		), TIMEOUT @TimeoutMS;
	END TRY
	BEGIN CATCH 
		-- If there are no more conversations to retrieve, 
		-- GET CONVERSATION GROUP will throw and execption 
		-- and we should stop iterating:
		BREAK;
	END CATCH;
END
/*
NOTE: RECEIVE will retrieve messages one conversation group at a time.
So, if you sent, let's say, 2 messages using the same conversation 
handle, then they'll both have the same conversation group and RECEIVE 
will retrieve both of them. The next time RECEIVE is issued, the next 
available conversation group will be retrieved.
*/
-- Check if anything was retrieved and process accordingly:
IF EXISTS(SELECT * FROM @InProcTable)
BEGIN
	DECLARE @status TINYINT, @service_instance_id UNIQUEIDENTIFIER,
		@conversation_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), 
		@conversation_group_id UNIQUEIDENTIFIER
	DECLARE c_DotNetFunQueueHandler CURSOR FOR 
		SELECT * FROM @InProcTable 
		ORDER BY conversation_group_id, message_sequence_number ASC
	OPEN c_DotNetFunQueueHandler
	FETCH NEXT FROM c_DotNetFunQueueHandler INTO 
		@status, @service_instance_id, @conversation_handle, 
		@message_sequence_number, @service_name, 
		@service_contract_name, @message_type_name, 
		@validation, @message_body, @conversation_group_id
	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- Let's do something with it here ...
		/*
		If RETENTION is ON, messages will be saved and status
		is set to 1 (dequeued).
		If the message is Ready, status is set to 0.
		If the message is disabled, status is set to 2.
		RECEIVE will always only retrieve messages with a 
		status of 0.
		*/
		SELECT @message_sequence_number AS message_sequence_number, 
			@status as status, 
			@conversation_group_id as conversation_group_id, 
			CAST(@message_body AS VARCHAR(MAX)) AS Message;
		-- End the conversation for the conversation group
		IF @validation = 'E'
			END CONVERSATION @conversation_handle;
		FETCH NEXT FROM c_DotNetFunQueueHandler INTO 
			@status, @service_instance_id, @conversation_handle, 
			@message_sequence_number, @service_name, 
			@service_contract_name, @message_type_name, 
			@validation, @message_body, @conversation_group_id
	END
	CLOSE c_DotNetFunQueueHandler
	DEALLOCATE c_DotNetFunQueueHandler
	/*
	NOTE: If RETENTION is set to ON for the QUEUE, you must send an 
	END CONVERSATION messages, otherwise the messages will be held 
	in the queue in a dequeued state.
	*/
END