输入参数:xml数据类型 功能:新增和修改 --value() 方法从 XML 中检索 rogue 属性值。然后将该值分配给 int 变量。
--将 Member 节点拆分成多行
- SELECT T.c.query('.') AS result
- FROM @x.nodes('/root/Member') T(c);
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
/****** Object: Procedure [dbo].[UP_ConfScheduled_AddScheduledConf] Script Date: 2014-3-17 9:16:26 ******/ USE [ytSummitTeleConf_DB]; GO SET ANSI_NULLS ON; GO SET QUOTED_IDENTIFIER ON; GO /*============================================================= =============================================================*/ CREATE PROCEDURE [dbo].[UP_ConfScheduled_AddScheduledConf] @BookConf XML, @MemberList XML AS SET NOCOUNT ON /* --预约会议信息 <Root> <ConfRoom>4F3A595F-DCAF-49DD-806F-1E4BA0F58D33</ConfRoom> <SeqNo>254941</SeqNo> <MasterMebID>C8C71FAE-8365-4501-8EC0-4C32120A5CE3</MasterMebID> <ConfTitle>2014-2-2会议</ConfTitle> <BookTime>2014-1-17 0:00:00</BookTime> <ConfType>1</ConfType> <AddConfMode>0</AddConfMode> <WriteTime>2014-1-17 11:56:27</WriteTime> <Status>10</Status> <IsRecord>0</IsRecord> </Root> --预约会议成员列表 <Root> <Member> <Phoneno>01052810000,8312</Phoneno> <Name>主持人</Name> <Mode>1</Mode> <MebGuid>C8C71FAE-8365-4501-8EC0-4C32120A5CE3</MebGuid> <AddTime>2014-1-17 11:55:02</AddTime> <IsModerator>True</IsModerator> </Member> </Root> */ DECLARE @ConfRoom UNIQUEIDENTIFIER, @BookTime DATETIME SELECT @BookTime = T.c.value('(./BookTime/text())[1]', 'DATETIME'), @ConfRoom = T.c.value('(./ConfRoom/text())[1]', 'UNIQUEIDENTIFIER') FROM @BookConf.nodes('Root') AS T(c) IF EXISTS ( SELECT 1 FROM dbo.WTC_TB_BOOKCONF WHERE CONFROOM = @ConfRoom ) BEGIN UPDATE A SET A.BookTime = B.BookTime, A.ConfTitle = B.ConfTitle, A.ConfType = B.ConfType, A.AddConfMode = B.AddConfMode, A.WRITETIME = B.WRITETIME, A.[Status] = B.[Status], A.IsRecord = B.IsRecord, A.MasterMebID = B.MasterMebID FROM dbo.WTC_TB_BOOKCONF A INNER JOIN ( SELECT BookTime = @BookTime, SeqNo = T.c.value('(./SeqNo/text())[1]', 'INT'), ConfRoom = @ConfRoom, ConfTitle = T.c.value('(./ConfTitle/text())[1]', 'VARCHAR(256)'), ConfType = T.c.value('(./ConfType/text())[1]', 'TINYINT'), AddConfMode = T.c.value('(./AddConfMode/text())[1]', 'TINYINT'), WRITETIME = T.c.value('(./WriteTime/text())[1]', 'DATETIME'), [Status] = T.c.value('(./Status/text())[1]', 'TINYINT'), IsRecord = T.c.value('(./IsRecord/text())[1]', 'TINYINT'), MasterMebID = T.c.value('(./MasterMebID/text())[1]', 'UNIQUEIDENTIFIER') FROM @BookConf.nodes('Root') AS T(c) ) B ON A.CONFROOM = B.ConfRoom END ELSE BEGIN INSERT INTO WTC_TB_BOOKCONF ( BOOKTIME, SeqNo, CONFROOM, CONFTITLE, CONFTYPE, ADDCONFMODE, WRITETIME, [STATUS], IsRecord, MasterMebID ) SELECT BookTime = T.c.value('(./BookTime/text())[1]', 'DATETIME'), SeqNo = T.c.value('(./SeqNo/text())[1]', 'INT'), @ConfRoom, ConfTitle = T.c.value('(./ConfTitle/text())[1]', 'VARCHAR(256)'), ConfType = T.c.value('(./ConfType/text())[1]', 'TINYINT'), AddConfMode = T.c.value('(./AddConfMode/text())[1]', 'TINYINT'), WRITETIME = T.c.value('(./WriteTime/text())[1]', 'DATETIME'), [Status] = T.c.value('(./Status/text())[1]', 'TINYINT'), IsRecord = T.c.value('(./IsRecord/text())[1]', 'TINYINT'), MasterMebID = T.c.value('(./MasterMebID/text())[1]', 'UNIQUEIDENTIFIER') FROM @BookConf.nodes('Root') AS T(c) END IF EXISTS ( SELECT 1 FROM dbo.WTC_TB_BOOKCONFQUEUE WHERE CONFROOM = @ConfRoom ) BEGIN UPDATE dbo.WTC_TB_BOOKCONFQUEUE SET BOOKTIME = @BookTime WHERE CONFROOM = @ConfRoom END ELSE BEGIN INSERT INTO dbo.WTC_TB_BOOKCONFQUEUE ( CONFROOM, BOOKTIME, [STATUS] ) VALUES ( @ConfRoom, @BookTime, 0 ) END IF EXISTS ( SELECT 1 FROM dbo.WTC_TB_BOOKMEMBER WITH(NOLOCK) WHERE CONFROOM = @ConfRoom ) BEGIN DELETE dbo.WTC_TB_BOOKMEMBER WHERE CONFROOM = @ConfRoom END INSERT INTO dbo.WTC_TB_BOOKMEMBER ( MebID, CONFROOM, PHONENO, PHONENOTE, MEMBERTYPE ) SELECT MebGuid = T.c.value('(./MebGuid/text())[1]', 'UNIQUEIDENTIFIER'), ConfRoom = @ConfRoom, Phoneno = T.c.value('(./Phoneno/text())[1]', 'VARCHAR(32)'), Name = T.c.value('(./Name/text())[1]', 'VARCHAR(128)'), Mode = T.c.value('(./Mode/text())[1]', 'TINYINT') FROM @MemberList.nodes('Root/Member') AS T(c) GO
取消预约会议-事务处理
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
/****** Object: Procedure [dbo].[UP_ConfScheduled_CancelScheduledConf] Script Date: 2014-3-17 9:23:26 ******/ USE [ytSummitTeleConf_DB]; GO SET ANSI_NULLS ON; GO SET QUOTED_IDENTIFIER ON; GO /*============================================================= =============================================================*/ CREATE PROCEDURE [dbo].[UP_ConfScheduled_CancelScheduledConf] @SeqNo INT, @ConfRoom UNIQUEIDENTIFIER AS SET NOCOUNT ON IF NOT EXISTS ( SELECT 1 FROM dbo.WTC_TB_BOOKCONF WITH(NOLOCK) WHERE CONFROOM = @ConfRoom AND [STATUS] = 0 ) BEGIN RETURN -1 --预约会议不存在. END IF EXISTS ( SELECT 1 FROM dbo.WTC_TB_USERCONF WITH(NOLOCK) WHERE CONFROOM = @ConfRoom ) BEGIN RETURN -2 --会议已经召开. END BEGIN TRY BEGIN TRAN --取消预约 UPDATE dbo.WTC_TB_BOOKCONF SET [STATUS] = 3 WHERE CONFROOM = @ConfRoom AND [STATUS] = 0 --删除预约队列 DELETE dbo.WTC_TB_BOOKCONFQUEUE WHERE CONFROOM = @ConfRoom --保留预约会议信息,以便统计短信等费用 INSERT INTO dbo.WTC_TB_USERCONF ( CONFROOM, SeqNo, CONFTITLE, CONFTIME, ISRECORD, MasterMebID, Flag ) SELECT A.CONFROOM, A.SeqNo, A.CONFTITLE, A.BOOKTIME, A.IsRecord, A.MasterMebID, 20 --取消的预约会议 FROM dbo.WTC_TB_BOOKCONF A WITH(NOLOCK) WHERE A.CONFROOM = @ConfRoom AND NOT EXISTS ( SELECT 1 FROM dbo.WTC_TB_USERCONF B WITH(NOLOCK) WHERE B.CONFROOM = A.CONFROOM ) INSERT INTO dbo.WTC_TB_USERCONFMEB ( MebID, CONFROOM, PHONENO, PHONENOTE, MEMBERTYPE, ADDCONFTIME, ADDORDER ) SELECT A.MebID, A.CONFROOM, A.PHONENO, A.PHONENOTE, A.MEMBERTYPE, GETDATE(), ROW_NUMBER() OVER(ORDER BY GETDATE()) FROM dbo.WTC_TB_BOOKMEMBER A WITH(NOLOCK) WHERE CONFROOM = @ConfRoom AND NOT EXISTS ( SELECT 1 FROM dbo.WTC_TB_USERCONFMEB B WITH(NOLOCK) WHERE B.CONFROOM = A.CONFROOM AND B.MebID = A.MebID ) --保留预约会议信息,以便统计短信等费用 INSERT INTO dbo.WTC_TB_CONFROOM ( CONFROOM, SeqNo, CONFID, CREATETIME, CREATEONLINEID, CONFTITLE, UPDATETIME, IsPlayMusic, CONFTYPE, CONFDATAKEY, RECORDTHREADID, IsRecord ) SELECT A.CONFROOM, A.SeqNo, 0, A.BOOKTIME, A.MasterMebID, A.CONFTITLE, GETDATE(), 0, 0, '', 0, A.IsRecord FROM dbo.WTC_TB_BOOKCONF A WITH(NOLOCK) WHERE A.CONFROOM = @ConfRoom AND NOT EXISTS ( SELECT 1 FROM dbo.WTC_TB_CONFROOM B WITH(NOLOCK) WHERE B.CONFROOM = A.CONFROOM ) INSERT INTO dbo.WTC_TB_CONFMEMBERS ( MebID, CONFROOM, PHONENO, DATA, UPDATETIME, ADDORDER ) SELECT A.MebID, A.CONFROOM, A.PHONENO, A.PHONENOTE, GETDATE(), ROW_NUMBER() OVER(ORDER BY GETDATE()) FROM dbo.WTC_TB_BOOKMEMBER A WITH(NOLOCK) WHERE CONFROOM = @ConfRoom AND NOT EXISTS ( SELECT 1 FROM dbo.WTC_TB_CONFMEMBERS B WITH(NOLOCK) WHERE B.CONFROOM = A.CONFROOM AND B.MebID = A.MebID ) COMMIT TRAN END TRY BEGIN CATCH ROLLBACK TRAN RETURN -100 --数据库异常 END CATCH GO
获取预约会议
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
/****** Object: Procedure [dbo].[UP_ConfScheduled_GetScheduledConfs] Script Date: 2014-3-17 9:29:19 ******/ USE [ytSummitTeleConf_DB]; GO SET ANSI_NULLS ON; GO SET QUOTED_IDENTIFIER ON; GO /*============================================================= =============================================================*/ CREATE PROCEDURE [dbo].[UP_ConfScheduled_GetScheduledConfs] @SeqNo INT AS SET NOCOUNT ON SELECT A.CONFROOM ,A.CONFTITLE ,A.BOOKTIME ,A.WRITETIME ,B.MemberCount ,A.IsRecord FROM dbo.WTC_TB_BOOKCONF A WITH(NOLOCK) OUTER APPLY ( SELECT MemberCount = COUNT(1) FROM dbo.WTC_TB_BOOKMEMBER B WITH(NOLOCK) WHERE B.CONFROOM = A.CONFROOM ) B WHERE A.SeqNo = @SeqNo AND A.[STATUS] = 0 ORDER BY A.BOOKTIME desc GO
修改参会模式
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
/****** Object: Procedure [dbo].[UP_ConfScheduled_SetParticipantTalkMode] Script Date: 2014-3-17 9:32:26 ******/ USE [ytSummitTeleConf_DB]; GO SET ANSI_NULLS ON; GO SET QUOTED_IDENTIFIER ON; GO /*============================================================= =============================================================*/ CREATE PROCEDURE dbo.UP_ConfScheduled_SetParticipantTalkMode @ConfRoom UNIQUEIDENTIFIER, @MemberList XML AS SET NOCOUNT ON /* <Root> <Member> <MebID>5685C2C9-70B7-4721-AB77-385FEDD7B0CF</MebID> <MemberType>1</MemberType> </Member> <Member> <MebID>E495548E-55FA-4588-AB9A-99CC7ED3D758</MebID> <MemberType>4</MemberType> </Member> </Root> */ UPDATE A SET A.MemberType = B.MemberType FROM dbo.WTC_TB_BOOKMEMBER A INNER JOIN ( SELECT MebID = T.c.value('(./MebID/text())[1]', 'UNIQUEIDENTIFIER'), MemberType = T.c.value('(./MemberType/text())[1]', 'TINYINT') FROM @MemberList.nodes('Root/Member') AS T(c) ) B ON A.MebID = B.MebID WHERE A.CONFROOM = @ConfRoom GO