spAdd_EventCalendarHighlightDetail

CREATE    PROCEDURE [dbo].[spAdd_EventCalendarHighlightDetail]
(
    @iEvtCalHighlightID            int,
    @sSrcCalIDString        varchar(max),
    @DisplayColors        varchar(max),
    @IconIDs        varchar(max)
)
AS
set nocount on
begin 

    -- 1: delete all existing source calendar selections
    DELETE FROM dbo.EventCalendarHighlightDetail 
    WHERE EventCalendarHighlightID = @iEvtCalHighlightID

    -- 2: insert the new source calendar selections
    DECLARE @iSrcCalID int
    DECLARE @color nvarchar(10)
    DECLARE @iconID int

    DECLARE @IDPos int
    DECLARE @ColorPos int
    DECLARE @IconPos int

    SET @IDPos = CHARINDEX(',',@sSrcCalIDString)
    SET @ColorPos = CHARINDEX(',',@DisplayColors)
    SET @IconPos = CHARINDEX(',',@IconIDs)
    WHILE @IDPos > 0 
        begin
            SET @iSrcCalID = CAST( LEFT(@sSrcCalIDString,@IDPos-1) AS int)
            SET @color = CAST( LEFT(@DisplayColors,@ColorPos-1) AS nvarchar(10))
            SET @iconID = CAST( LEFT(@IconIDs,@IconPos-1) AS int)
            INSERT INTO [dbo].[EventCalendarHighlightDetail]
            (
                EventCalendarHighlightID,
                SrcCalendarID,
                DisplayColor,
                IconID
            )
            VALUES
            (
                @iEvtCalHighlightID,
                @iSrcCalID,
                @color,
                @iconID
            )
            SET @sSrcCalIDString = SUBSTRING(@sSrcCalIDString,@IDPos+1,LEN(@sSrcCalIDString)-@IDPos)
            SET @DisplayColors = SUBSTRING(@DisplayColors,@ColorPos+1,LEN(@DisplayColors)-@ColorPos)
            SET @IconIDs = SUBSTRING(@IconIDs,@IconPos+1,LEN(@IconIDs)-@IconPos)
            SET @IDPos = CHARINDEX(',',@sSrcCalIDString)
            SET @ColorPos = CHARINDEX(',',@DisplayColors)
            SET @IconPos = CHARINDEX(',',@IconIDs)
        end
    IF (LEN(@sSrcCalIDString) > 0)
        INSERT INTO [dbo].[EventCalendarHighlightDetail]
        (
            EventCalendarHighlightID,
            SrcCalendarID,
            DisplayColor,
            IconID
        )
        VALUES
        (
            @iEvtCalHighlightID,
            @sSrcCalIDString,
            @DisplayColors,
            @IconIDs
        )
end