USP_CALENDARCATALOG_CREATEORUPDATE

            CREATE procedure dbo.USP_CALENDARCATALOG_CREATEORUPDATE (
                @ID uniqueidentifier,
                @NAME nvarchar(100),
                @DESCRIPTION nvarchar(255),
                @CONTEXTRECORDTYPE nvarchar(255),
                @CALENDARSPECXML xml,                
                @CHANGEAGENTID uniqueidentifier = null
            ) as begin
                if @CHANGEAGENTID is null  
                    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

                declare @CONTEXTRECORDTYPEID uniqueidentifier;

                if len(@CONTEXTRECORDTYPE) > 0
                begin
                    exec dbo.USP_RECORDTYPE_GETID_OR_CREATE_FROMNAME @NAME = @CONTEXTRECORDTYPE, @CHANGEAGENTID = @CHANGEAGENTID, @RECORDTYPEID = @CONTEXTRECORDTYPEID output;
                end

                if @ID is null or @ID = '00000000-0000-0000-0000-000000000000'
                begin
                    set @ID = newid()
                end

                if exists(select ID from dbo.CALENDARCATALOG where ID=@ID)
                    update dbo.CALENDARCATALOG set
                        ID = @ID,
                        NAME = @NAME,
                        DESCRIPTION = @DESCRIPTION,
                        CONTEXTRECORDTYPEID = @CONTEXTRECORDTYPEID,
                        CALENDARSPECXML = @CALENDARSPECXML,
                        CHANGEDBYID = @CHANGEAGENTID
                    where dbo.CALENDARCATALOG.ID = @ID
                else
                    insert into dbo.CALENDARCATALOG
                    (
                        ID,
                        NAME,
                        DESCRIPTION,
                        CONTEXTRECORDTYPEID,
                        CALENDARSPECXML,
                        ADDEDBYID,
                        CHANGEDBYID
                    )
                    values
                    (
                        @ID,
                        @NAME,
                        @DESCRIPTION,
                        @CONTEXTRECORDTYPEID,
                        @CALENDARSPECXML,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID
                    )

                -- Assign Default Color Styles for calendar specs which do not define them

                declare @DATASOURCES_TABLE table
                (
                    ID uniqueidentifier,
                    COLORSTYLECODEID integer,
                    [SEQUENCE] integer IDENTITY(1,1)
                )

                insert into @DATASOURCES_TABLE
                (
                    ID,
                    COLORSTYLECODEID
                )
                select
                    CAST(CAST(item.query('data(@DataListID)') as varchar(36)) as uniqueidentifier) as ID,
                    COALESCE(
                        CALENDARDATALISTCOLOR.COLORSTYLECODE, 
                        dbo.UFN_CALENDARCATALOGCOLOR_GETID(CAST(item.query('data(@ColorStyle)') as nvarchar(20)))
                    ) as COLORSTYLECODEID
                from dbo.CALENDARCATALOG
                    cross apply CALENDARSPECXML.nodes('//*:CalendarDataSource') as DataSources(item)
                        left join CALENDARDATALISTCOLOR on CALENDARCATALOG.ID = CALENDARDATALISTCOLOR.CALENDARCATALOGID
                            and CAST(item.query('data(@DataListID)') as nvarchar(36)) = CALENDARDATALISTCOLOR.DATALISTID
                where dbo.CALENDARCATALOG.ID = @ID

                if exists(select ID from @DATASOURCES_TABLE where COLORSTYLECODEID is null)
                begin

                    insert into dbo.CALENDARDATALISTCOLOR
                    (
                        CALENDARCATALOGID,
                        DATALISTID,
                        COLORSTYLECODE,
                        ADDEDBYID,
                        CHANGEDBYID
                    )
                    select
                        @ID,
                        DATALISTS_WITHOUT_STYLE.ID,
                        UNUSED_STYLES.ID,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID        
                    from (
                        select DATASOURCES_TABLE.ID, ROW_NUMBER() over (ORDER BY DATASOURCES_TABLE.[SEQUENCE]) as [SEQUENCE]
                        from @DATASOURCES_TABLE DATASOURCES_TABLE
                        where DATASOURCES_TABLE.COLORSTYLECODEID is null
                    ) DATALISTS_WITHOUT_STYLE
                    inner join
                    (
                        select COLORSTYLECODE.ID, ROW_NUMBER() over (ORDER BY COLORSTYLECODE.ID) as [SEQUENCE]
                        from (
                            SELECT 0 AS ID UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
                            SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL
                            SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL
                            SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19
                        ) COLORSTYLECODE
                        left join @DATASOURCES_TABLE DATASOURCES_TABLE on DATASOURCES_TABLE.COLORSTYLECODEID = COLORSTYLECODE.ID
                        where DATASOURCES_TABLE.COLORSTYLECODEID is null
                    ) UNUSED_STYLES
                    on DATALISTS_WITHOUT_STYLE.SEQUENCE = UNUSED_STYLES.SEQUENCE

                end

            end