Dynamic SQL vs Parameterized dynamic SQL and SQL Server Service Broker

DECLARE @parameter integer = 1;

-- Dynamic SQL
DECLARE @sql nvarchar(max) =
    N'
    SELECT *
    FROM AdventureWorks2008R2.Production.Product AS p
    WHERE p.ProductID = ' + CONVERT(varchar(12), @parameter);

EXECUTE (@sql);

-- Parameterized dynamic SQL
EXECUTE sys.sp_executesql
    @statement =
        N'
        SELECT *
        FROM AdventureWorks2008R2.Production.Product AS p
        WHERE p.ProductID = @ProductID;',
    @params = N'@ProductID integer',
    @ProductID = @parameter;

Dynamic sql is simply building a executing against your database. This is open to sql injection. Parameterized query is using variable to hold the values for your query. The big advantage is the query engine will use the supplied value as the datatype and as such is not prone to sql injection. 


============================================================================

SQL Server Service Broker

Service Broker came with SQL Server 2005 that provides messaging and queue functionality between instances. Basic function is sending and receiving messages.

Broker conversation is considered as a Dialog between two participants.

Service Broker can send messages to different databases or different servers. It uses TCP/IP to send messages.

Service Broker is useful to separate applications such as separate Audit Logging.

Setup a Service Broker

Following are the steps to setup Service Broker.

Step 1: Enable Service Broker for the database. By default, it has been disabled.

-- Enable Service Broker

ALTER DATABASE ServiceBrokerDB SET ENABLE_BROKER

GO

Step 2: Create a Message Type. This is a template for messages that you are sending.

-- Create Message Type

CREATE MESSAGE TYPE SBMessageType_TableChange VALIDATION = NONE;

Step 3: Create a Contract that is used to Send and Receive messages. 

-- Create Contract

CREATE CONTRACT SBContract_TableChange (SBMessageType_TableChange SENT BY INITIATOR);

Step 4: Create two Queues to send and receive messages. The Sender Queue is used to send messages and Recipient Queue to poll messages. 

-- Create Send Queue

CREATE QUEUE dbo.SBSendQueue_TableChange

-- Create Receive Queue

CREATE QUEUE SBReceiveQueue_TableChange

Step 5: Create two Services for each Queue. To create a service, we need Queues and Contract. 

-- Create Services

CREATE SERVICE SBSendService_TableChange ON QUEUE dbo.SBSendQueue_TableChange(SBContract_TableChange);

CREATE SERVICE SBReceiveService_TableChange ON QUEUEdbo.SBReceiveQueue_TableChange(SBContract_TableChange);

Now the Service Broker has been created. Now check to send a message. 

Send & Receive Messages

Send a Message

-- Send Message

DECLARE @conversation uniqueidentifier, @msg varchar(max)='My Message 1';

--- Start a conversation using DIALOG:

BEGIN DIALOG @conversation

    FROM SERVICE SBSendService_TableChange

    TO SERVICE N'SBReceiveService_TableChange'

    ON CONTRACT SBContract_TableChange

    WITH ENCRYPTION=OFF;

--- Send the message

SEND ON CONVERSATION @conversation

       MESSAGE TYPE SBMessageType_TableChange (@msg);

View Messages available in the Receive Queue. 

-- View messages from Receive Queue

SELECT CONVERT(VARCHAR(MAX), message_body) AS Message, message_type_name

FROM SBReceiveQueue_TableChange

GO

You can see messages any number of times. Since it is not pulling, messages are not removing from the Receive Queue.

Receive (Polling) Messages from the Receive Queue

-- Receiving Message

DECLARE @conversation uniqueidentifier, @senderMsgType nvarchar(100), @msg varchar(max);

WAITFOR (

    RECEIVE TOP(1)

        @conversation = conversation_handle,

        @msg = message_body,

        @senderMsgType = message_type_name

    FROM SBReceiveQueue_TableChange), TIMEOUT 1000;

       IF @conversation IS NULL

              BEGIN

                     return;

              END   

SELECT @msg AS RecievedMessage, @senderMsgType AS SenderMessageType;

END CONVERSATION @conversation;

GO

One you receive the messages, it will remove from the queue. 

Add incoming messages to a Table using a Stord Procedure

In here I’m going to write a stored procedure to run automatically when message is received to the Queue. The stored procedure insert that message to an another table.

This is called “internal activation”.

Create a Table

-- Create a Table

CREATE TABLE dbo.TableChangeMessage

(

       MessageId int IDENTITY(1,1) NOT NULL,

       DateInserted datetime NOT NULL,

       SBMessage varchar(max),

       CONSTRAINT PK_TableChangeMessage PRIMARY KEY (MessageId)

)

GO

Create a Stored Procedure to Insert messages to the “TableChangeMessage” table.

-- Create a Stored Procedure

CREATE PROCEDURE dbo.ReadTableChangeMessage

AS

BEGIN

       DECLARE @conversation uniqueidentifier, @senderMsgType nvarchar(100), @msg varchar(max);

       WAITFOR (

    RECEIVE TOP(1)

        @conversation = conversation_handle,

        @msg = message_body,

        @senderMsgType = message_type_name

              FROM SBReceiveQueue_TableChange), TIMEOUT 1000;

       IF @conversation IS NULL

              return;      

       IF (@senderMsgType='SBMessageType_TableChange')

       BEGIN

              INSERT INTO dbo.TableChangeMessage (DateInserted, SBMessage)

              SELECT GETDATE(), @msg;

       END

       END CONVERSATION @conversation;

END

GO

Modify Receive Queue to run above Stored Procedure every time a message arrives. 

-- Modify Receive Queue

ALTER QUEUE SBReceiveQueue_TableChange WITH ACTIVATION (

    STATUS = ON,

    PROCEDURE_NAME = dbo.ReadTableChangeMessage,

    EXECUTE AS SELF,

    MAX_QUEUE_READERS = 1);

GO

Now check weather data is available in the table.

SELECT * FROM [dbo].[TableChangeMessage]

GO

Send Messages to Service Broker when Insert/Update/Delete operation

In this case, I’m sending the updated table name to the Service Broker. (Send Messages)

Create two tables

-- Create table Custome

CREATE TABLE [dbo].[Customer](

       [Id] [int] IDENTITY(1,1) NOT NULL,

       [Name] [varchar](100) NULL,

       [Address] [varchar](500) NULL,

       [Telephone] [varchar](50) NULL,

       [Email] [varchar](50) NULL,

       [DateUpdated] [datetime] NOT NULL DEFAULT (getdate()),

       [UserUpdated] [varchar](50) NOT NULL DEFAULT ('admin'),

       CONSTRAINT [PK_Customer] PRIMARY KEY ([Id])

)

GO

-- Create table Employee

CREATE TABLE [dbo].[Employee](

       [Id] [int] IDENTITY(1,1) NOT NULL,

       [Name] [varchar](100) NULL,

       [NIC] [varchar](50) NULL,

       [Designation] [varchar](100) NULL,

       [DateUpdated] [datetime] NOT NULL DEFAULT (getdate()),

       [UserUpdated] [varchar](50) NOT NULL DEFAULT ('admin'),

       CONSTRAINT [PK_Employee] PRIMARY KEY ([Id])

)

GO

Create a Stored Procedure to send messages to Service Broker

-- Stored Procedure to Send Message.

CREATE PROCEDURE dbo.Send_TableChange_Message

       @TableName nvarchar(100) = N''

AS

BEGIN

       DECLARE @conversation uniqueidentifier, @msg varchar(max) = @TableName;

       --- Start a conversation:

       BEGIN DIALOG @conversation

              FROM SERVICE SBSendService_TableChange

              TO SERVICE N'SBReceiveService_TableChange'

              ON CONTRACT SBContract_TableChange

              WITH ENCRYPTION=OFF;

       --- Send the message

       SEND ON CONVERSATION @conversation

              MESSAGE TYPE SBMessageType_TableChange (@msg);

END

Create two Triggers to Send Messages to Service Broker

-- Trigger to trigger for Customer table to execute the SP.

CREATE TRIGGER TRG_Customer

ON dbo.Customer

FOR INSERT, UPDATE, DELETE 

AS

DECLARE @TableName NVARCHAR(100) = 'Customer';

EXEC dbo.Send_TableChange_Message @TableName;

GO

-- Trigger to trigger for Employee table to execute the SP.

CREATE TRIGGER TRG_Employee

ON dbo.Employee

FOR INSERT, UPDATE, DELETE 

AS

DECLARE @TableName NVARCHAR(100) = 'Employee';

EXEC dbo.Send_TableChange_Message @TableName;

GO

Now add values to the “Customer” and “Employee” tables. Then check the “TableChangeMessage” table. 

SELECT * FROM [dbo].[TableChangeMessage]

GO

You can see the values are inserting when you Add, Update or delete values to “Customer” or “Employee” tables.