笔记22 service broker例子

  1 --service broker例子
  2 --http://www.builder.com.cn/2007/0302/379424.shtml
  3 USE master
  4 
  5 GO
  6 
  7 IF EXISTS ( SELECT  *
  8             FROM    sys.databases
  9             WHERE   name = 'SB' )
 10     DROP DATABASE SB
 11 
 12 GO
 13 
 14 CREATE DATABASE SB
 15 
 16 GO
 17 
 18 ALTER DATABASE SB
 19 
 20 SET ENABLE_BROKER
 21 
 22 GO
 23 
 24 USE SB;
 25 
 26 GO
 27 
 28 CREATE TABLE Sales
 29     (
 30       SaleID INT IDENTITY(1, 1) ,
 31       SaleDate SMALLDATETIME ,
 32       SaleAmount MONEY ,
 33       ItemsSold INT
 34     );
 35 
 36 GO
 37 
 38 USE [SB]
 39 CREATE MESSAGE TYPE [RecordSale] VALIDATION = NONE;
 40 
 41 CREATE CONTRACT [SalesContract]
 42 
 43 (
 44 
 45 [RecordSale] SENT BY INITIATOR
 46 
 47 );
 48 
 49 GO
 50 USE [SB]
 51 
 52 CREATE QUEUE [SalesQueue];
 53 
 54 CREATE SERVICE [SalesService] ON QUEUE [SalesQueue]([SalesContract]);
 55 
 56 GO
 57 
 58 CREATE QUEUE [RecordSalesQueue];
 59 
 60 CREATE SERVICE [RecordSalesService] ON QUEUE [RecordSalesQueue];
 61 
 62 GO
 63 
 64 
 65 CREATE PROCEDURE usp_RecordSaleMessage
 66 AS
 67     BEGIN
 68         SET NOCOUNT ON;
 69         DECLARE @Handle UNIQUEIDENTIFIER;
 70         DECLARE @MessageType SYSNAME;
 71         DECLARE @Message XML
 72         DECLARE @SaleDate DATETIME
 73         DECLARE @SaleAmount MONEY
 74         DECLARE @ItemsSold INT;
 75         RECEIVE TOP (1)                   @Handle = conversation_handle,                  @MessageType = message_type_name,                   @Message = message_body            FROM [SalesQueue];
 76         IF ( @Handle IS NOT NULL
 77              AND @Message IS NOT NULL
 78            )
 79             BEGIN
 80                 SELECT  @SaleDate = CAST(CAST(@Message.query('/Params/SaleDate/text()') AS NVARCHAR(MAX)) AS DATETIME)
 81                 SELECT  @SaleAmount = CAST(CAST(@Message.query('/Params/SaleAmount/text()') AS NVARCHAR(MAX)) AS MONEY)
 82                 SELECT  @ItemsSold = CAST(CAST(@Message.query('/Params/ItemsSold/text()') AS NVARCHAR(MAX)) AS INT)
 83                 INSERT  INTO Sales
 84                         ( SaleDate ,
 85                           SaleAmount ,
 86                           ItemsSold
 87                         )
 88                 VALUES  ( @SaleDate ,
 89                           @SaleAmount ,
 90                           @ItemsSold
 91                         );
 92             END
 93     END
 94 GO
 95 
 96 ALTER QUEUE [SalesQueue] WITH ACTIVATION
 97 
 98 (
 99 
100 STATUS = ON,
101 
102 MAX_QUEUE_READERS = 1,
103 
104 PROCEDURE_NAME = usp_RecordSaleMessage,
105 
106 EXECUTE AS OWNER
107 
108 );
109 
110 GO
111 
112 
113 CREATE PROCEDURE usp_SendSalesInfo
114     (
115       @SaleDate SMALLDATETIME ,
116       @SaleAmount MONEY ,
117       @ItemsSold INT
118     )
119 AS
120     BEGIN
121         DECLARE @MessageBody XML
122         CREATE TABLE #ProcParams
123             (
124               SaleDate SMALLDATETIME ,
125               SaleAmount MONEY ,
126               ItemsSold INT
127             )
128         INSERT  INTO #ProcParams
129                 ( SaleDate ,
130                   SaleAmount ,
131                   ItemsSold
132                 )
133         VALUES  ( @SaleDate ,
134                   @SaleAmount ,
135                   @ItemsSold
136                 )
137         SELECT  @MessageBody = ( SELECT *
138                                  FROM   #ProcParams
139                                FOR
140                                  XML PATH('Params') ,
141                                      TYPE
142                                );
143         DECLARE @Handle UNIQUEIDENTIFIER;
144         BEGIN DIALOG CONVERSATION @Handle      FROM SERVICE
145             [RecordSalesService]      TO SERVICE 'SalesService'      ON CONTRACT
146             [SalesContract]      WITH ENCRYPTION = OFF;
147         SEND ON CONVERSATION @Handle       MESSAGE TYPE [RecordSale](@MessageBody);
148     END
149 GO
150 
151 
152 
153 
154 
155 
156 
157 EXECUTE usp_SendSalesInfo '1/9/2005', 30, 90
158 
159 --执行这个过程后，运行下面的SELECT语句看Sales表中是否插入一条记录。
160 
161 SELECT  * FROM    Sales;