SQL存储过程-新增和修改,参数Xml数据类型

输入参数:xml数据类型功能:新增和修改 --value() 方法从 XML 中检索 rogue 属性值。然后将该值分配给 int 变量。

--将 Member 节点拆分成多行

SELECT T.c.query('.') AS result
FROM @x.nodes('/root/Member') T(c);

/****** 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

View Code

取消预约会议-事务处理

/****** 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

View Code

获取预约会议

/****** 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

View Code

修改参会模式

/****** 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

View Code