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

/****** 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 DATETIMESELECT
@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)
ENDIF 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
)
ENDIF EXISTS
(
SELECT 1
FROM dbo.WTC_TB_BOOKMEMBER WITH(NOLOCK)
WHERE CONFROOM = @ConfRoom
)
BEGIN
DELETE dbo.WTC_TB_BOOKMEMBER
WHERE CONFROOM = @ConfRoom
ENDINSERT 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

/****** 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 ONIF NOT EXISTS
(
SELECT 1
FROM dbo.WTC_TB_BOOKCONF WITH(NOLOCK)
WHERE CONFROOM = @ConfRoom
AND [STATUS] = 0
)
BEGIN
RETURN -1 --预约会议不存在.
ENDIF EXISTS
( SELECT 1
FROM dbo.WTC_TB_USERCONF WITH(NOLOCK)
WHERE CONFROOM = @ConfRoom
)
BEGIN
RETURN -2 --会议已经召开.
ENDBEGIN 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 CATCHGO

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

/****** 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 = @ConfRoomGO