SQL Server, ServicerBroker for asynchronized execution of stored procedures
At PROCEDURE proc_BrokerTargetActivProc we have infinite loop: WHILE
  (1=1). Why ?


The process for receiving messages in SQL Server requires what I call a semiactive polling. The code makes a request to the queue for the next message, then waits for a response. That is what the WAITFOR block is doing.

WAITFOR 
( RECEIVE TOP(1) 
    @RecvReqDlgHandle = conversation_handle, 
    @RecvReqMsg = message_body, 
    @RecvReqMsgName = message_type_name 
  FROM TASK_QUEUE 
), TIMEOUT 5000; 


If the process was not in an infinite loop, the procedure would poll once, either receive a message or timeout, then quit. With the infinite loop, the procedure polls continuously until stopped.


  ExecuteProcedureAsync push to queue message with name of procedure to
  execute. What now ? How does it work that BrokerTargetActivProc will
  be called with exactly one message ?


When ExecuteProcedureAsync is called, it places the message in an asynchronous message queue. This is a process that is external to SQL Server, accepts messages from sending processes, and forwards them to the receiving process when it is ready for the next message (that is, when the receiver polls the queue for the next message on the queue). The reference to BrokerTargetActiveProc when putting the message on the queue is letting the queue know where the message is going. It is not an interrupt that triggers the BrokerTargetActiveProc procedure.  

In an asynchronous queue like this, if the receiver is down, then the messages simply wait in the queue until the receiver comes back up or the queue runs out of resources to hold messages.