Skip to main content

Stored Procedure Internal Activation in Service Broker - SQL Server

As we have seen earlier posts for Service Broker for single database and Service Broker between databases, we have implemented it with examples. Hope you liked those posts.

Remember, in service broker example for a single database, we have created stored procedure to process requested data from inside the target database sent from initiator database. Then we have manually ran the stored procedure to process history data. Please open the link for that example here. We have tested the same there as follows,
USE SourceDB
GO

TRUNCATE TABLE TransactionTable
TRUNCATE TABLE TransactionHistory
GO

-- Inserting a record
INSERT INTO TransactionTable
SELECT 2,'Insert',Getdate()

-- Updating a records
UPDATE TransactionTable
SET TranType = 'Update',
TranDate = GETDATE()
WHERE TranId = 2

-- Deleting a record
DELETE FROM TransactionTable
WHERE TranId = 2

GO

-- Executing stored procedure manualy
EXEC SB_ReceiveData
GO

SELECT 
* 
FROM TransactionTable

SELECT 
* 
FROM TransactionHistory
GO
Now come out from the link and look what i will do?

Instead of running that stored procedure manually as above, we can set stored procedure self executing as follows. Whenever the new incoming requested messages come this stored procedure executed and process the data for history. We will alter the target queue here. This is called the internal activation of the stored procedure. Service broker will act the stored procedure to process incoming messages from target queue and will call anytime when messages arrive in the target queue.
-- Altering queue with self execution of stored procedure
ALTER QUEUE DestinationQueue
    WITH ACTIVATION
    ( STATUS = ON,
      PROCEDURE_NAME = SB_ReceiveData,
      MAX_QUEUE_READERS = 100,
      EXECUTE AS SELF
    )
GO
Re-testing :
As retesting the automated self execution of stored procedure, we will do the same, but we will not call the stored procedure.
USE SourceDB
GO

-- Truncating table
TRUNCATE TABLE TransactionTable
TRUNCATE TABLE TransactionHistory
GO

-- Inserting a record
INSERT INTO TransactionTable
SELECT 2,'Insert',Getdate()

-- Updating a records
UPDATE TransactionTable
SET TranType = 'Update',
TranDate = GETDATE()
WHERE TranId = 2

-- Deleting a record
DELETE FROM TransactionTable
WHERE TranId = 2

GO

-- Reviewing the data from tables
SELECT 
* 
FROM TransactionTable

SELECT 
* 
FROM TransactionHistory
GO
Finally the output coming for both testing of manually and salf execution of the stored procedure as captured following,


Hope you liked this post for setting stored procedure self execution with target queue.

Comments