xml是什么入参格式 存储过程_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]

@BookConfXML,@MemberListXMLAS

SET NOCOUNT ON

/*--预约会议信息

4F3A595F-DCAF-49DD-806F-1E4BA0F58D33

254941

C8C71FAE-8365-4501-8EC0-4C32120A5CE3

2014-2-2会议

2014-1-17 0:00:00

1

0

2014-1-17 11:56:27

10

0

--预约会议成员列表

01052810000,8312

主持人

1

C8C71FAE-8365-4501-8EC0-4C32120A5CE3

2014-1-17 11:55:02

True

*/

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') AST(c)IF EXISTS(SELECT 1

FROMdbo.WTC_TB_BOOKCONFWHERE CONFROOM = @ConfRoom)BEGIN

UPDATEASET 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.MasterMebIDFROMdbo.WTC_TB_BOOKCONF AINNER JOIN(SELECTBookTime= @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') AST(c)

) BON A.CONFROOM =B.ConfRoomEND

ELSE

BEGIN

INSERT INTOWTC_TB_BOOKCONF

(

BOOKTIME,

SeqNo,

CONFROOM,

CONFTITLE,

CONFTYPE,

ADDCONFMODE,

WRITETIME,[STATUS],

IsRecord,

MasterMebID

)SELECTBookTime= 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') AST(c)END

IF EXISTS(SELECT 1

FROMdbo.WTC_TB_BOOKCONFQUEUEWHERE CONFROOM = @ConfRoom)BEGIN

UPDATEdbo.WTC_TB_BOOKCONFQUEUESET BOOKTIME = @BookTime

WHERE CONFROOM = @ConfRoom

END

ELSE

BEGIN

INSERT INTOdbo.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

DELETEdbo.WTC_TB_BOOKMEMBERWHERE CONFROOM = @ConfRoom

END

INSERT INTOdbo.WTC_TB_BOOKMEMBER

(

MebID,

CONFROOM,

PHONENO,

PHONENOTE,

MEMBERTYPE

)SELECTMebGuid= 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') AST(c)GO