spAddUpdate_DataSources

CREATE PROC [dbo].[spAddUpdate_DataSources]
@PKID int OUTPUT,
@BackOfficeSystemID int,
@Name nvarchar(255),
@FieldNames xml

as

DECLARE @NewDataSourceID int

IF @PKID <= 0 
    BEGIN

    -- generate next uploaded datasource id available
                SELECT @NewDataSourceID=coalesce(max([DataSourceID])+1, 1001) 
                FROM dbo.DataSources 
                WHERE [DataSourceID]>1000

                INSERT INTO [dbo].[DataSources]
                           ([DataSourceID]
                           ,[Name]
                           ,[TypeNamespace]
                           ,[TypeName]
                           ,[IsEmailListSource]
                           ,[BackOfficeSystemId]
                           ,[Available]
                           ,[SubscriberProviderForSystemID]
                           ,[ImportListID]
                            ,[UploadOrigin])
                VALUES(
                       @NewDataSourceID,
                       @Name,
                       'Blackbaud.Web.Content.Core',
                       'Blackbaud.Web.Content.Core.DataSourceBBDM',
                       0,
                       @BackOfficeSystemID,
                       1,
                       -1,
                       NULL,
                        1)

            INSERT INTO dbo.BBDM_Fields (DataSourceID,[Name],IsEmailAddress,IsEmailDisplay, IsUnique, IsFinderNumber)
            SELECT @NewDataSourceID, X.Fields.value('Name[1]', 'nvarchar(255)') [Name],
            X.Fields.value('IsEmailAddress[1]', 'bit') IsEmailAddress, 
            X.Fields.value('IsEmailDisplay[1]', 'bit') IsEmailDisplay,
            X.Fields.value('IsUniqueIdentifier[1]', 'bit') IsUnique,
            X.Fields.value('IsFinderNumber[1]', 'bit') IsFinderNumber
            FROM @FieldNames.nodes('/ArrayOfFieldInfo/FieldInfo') X(fields)

            SET @PKID = @NewDataSourceID
    END
ELSE
    BEGIN
            UPDATE DataSources
            SET [Name] = @Name
            WHERE DataSourceID = @PKID

            INSERT INTO dbo.BBDM_Fields (DataSourceID,[Name],IsEmailAddress,IsEmailDisplay, IsUnique, IsFinderNumber)
            SELECT @PKID, X.Fields.value('Name[1]', 'nvarchar(255)') [Name],
            X.Fields.value('IsEmailAddress[1]', 'bit') IsEmailAddress, 
            X.Fields.value('IsEmailDisplay[1]', 'bit') IsEmailDisplay,
            X.Fields.value('IsUniqueIdentifier[1]', 'bit') IsUnique,
            X.Fields.value('IsFinderNumber[1]', 'bit') IsFinderNumber
            FROM @FieldNames.nodes('/ArrayOfFieldInfo/FieldInfo') X(fields) 
            WHERE NOT EXISTS (SELECT NULL FROM BBDM_Fields WHERE [Name] = X.Fields.value('Name[1]', 'nvarchar(255)') AND DataSourceID = @PKID)
    END