利用XML向存储过程传递复杂参数解决方案

IF EXISTS (
       SELECT *
       FROM   sys.objects
       WHERE  OBJECT_ID = OBJECT_ID(N'[dbo].[CreateScheduleConference]')
              AND TYPE IN (N'P', N'PC')
   )
    DROP PROCEDURE [dbo].[CreateScheduleConference]
GO

CREATE PROC [dbo].[CreateScheduleConference]
(@ResouceIds VARCHAR(MAX), @AttendeeList NVARCHAR(MAX))
AS
BEGIN
 SET NOCOUNT ON
 DECLARE @i INT
 IF @ResouceIds IS NOT NULL
    AND LEN(LTRIM(@ResouceIds)) > 0
 BEGIN
     EXEC sys.sp_xml_preparedocument @i OUTPUT,
          @ResouceIds
    
     SELECT R.ResourceID
     FROM   OPENXML(@i, '/RT/RS', 1) WITH(ResourceID INT '@ID') AS R
    
     EXEC sys.sp_xml_removedocument @i
 END 
 
 SET @i = 0
 IF @AttendeeList IS NOT NULL
    AND LEN(LTRIM(@AttendeeList)) > 0
 BEGIN
     EXEC sys.sp_xml_preparedocument @i OUTPUT,
          @AttendeeList
    
     SELECT A.UserId,
            A.Password,
            A.ConferenceRole
     FROM   OPENXML(@i, '/RT/IT', 1) WITH(
                UserId VARCHAR(20) '@U',
                [Password] VARCHAR(50) '@P',
                ConferenceRole VARCHAR(10) '@R'
            ) AS A
    
     EXEC sys.sp_xml_removedocument @i
 END
 
 SET NOCOUNT OFF
END

 

---test eg:

 

DECLARE @ResourceIds   VARCHAR(MAX)
DECLARE @AttendeeList  NVARCHAR(MAX)

SET @ResourceIds = '
<RT>
<RS ID="1000" /><RS ID="1001" /><RS ID="1002" /><RS ID="1003" /><RS ID="1004" /><RS ID="1005" /><RS ID="1006" /><RS ID="1007" /><RS ID="1008" /><RS ID="1009" />
</RT>'

SET @AttendeeList =
    '
<RT>
<IT U="test000001" P="pwd000001" R="1" /><IT U="test000002" P="pwd000002" R="2" /><IT U="test000003" P="pwd000003" R="3" /><IT U="test000004" P="pwd000004" R="4" /><IT U="test000005" P="pwd000005" R="5" /><IT U="test000006" P="pwd000006" R="6" /><IT U="test000007" P="pwd000007" R="7" /><IT U="test000008" P="pwd000008" R="8" /><IT U="test000009" P="pwd000009" R="9" />
</RT>'