spAdd_EventCalendarViewDetail

CREATE   PROCEDURE [dbo].[spAdd_EventCalendarViewDetail]
(
    @iEvtCalViewID            int,
    @sSubCalIDString        nvarchar(max),
    @sDisplayColorString    nvarchar(max),
    @sIconIDString            nvarchar(max)
)
AS
set nocount on
begin 

    begin transaction

    -- 1: delete all existing subcalendar selections
    DELETE FROM dbo.EventCalendarViewDetail 
    WHERE EventCalendarViewID = @iEvtCalViewID

    -- 2: insert the new subcalendar selections
    DECLARE @iSubCalPos int
    DECLARE @iSubCalID int
    DECLARE @iColorPos int
    DECLARE @sColor nvarchar(10)
    DECLARE @iIconPos int
    DECLARE @iIconID int

    SET @iSubCalPos = CHARINDEX(',',@sSubCalIDString)
    SET @iColorPos = CHARINDEX(',',@sDisplayColorString)
    SET @iIconPos = CHARINDEX(',',@sIconIDString)
    WHILE @iSubCalPos > 0 
        begin
            SET @iSubCalID = CAST( LEFT(@sSubCalIDString,@iSubCalPos-1) AS int)
            SET @sColor = CAST( LEFT(@sDisplayColorString,@iColorPos-1) AS nvarchar(10))
            SET @iIconID = CAST( LEFT(@sIconIDString,@iIconPos-1) AS int)
            INSERT INTO [dbo].[EventCalendarViewDetail]
            (
                   EventCalendarViewID,
                SubcalendarID,
                DisplayColor,
                IconID
            )
            VALUES
            (
                   @iEvtCalViewID,
                @iSubCalID,
                @sColor,
                @iIconID
            )
            SET @sSubCalIDString = SUBSTRING(@sSubCalIDString,@iSubCalPos+1,LEN(@sSubCalIDString)-@iSubCalPos)
            SET @sDisplayColorString = SUBSTRING(@sDisplayColorString,@iColorPos+1,LEN(@sDisplayColorString)-@iColorPos)
            SET @sIconIDString = SUBSTRING(@sIconIDSTring,@iIconPos+1,LEN(@sIconIDString)-@iIconPos)
            SET @iSubCalPos = CHARINDEX(',',@sSubCalIDString)
            SET @iColorPos = CHARINDEX(',',@sDisplayColorString)
            SET @iIconPos = CHARINDEX(',',@sIconIDString)
        end
    IF (LEN(@sSubCalIDString) > 0)
        INSERT INTO [dbo].[EventCalendarViewDetail]
        (
            EventCalendarViewID,
            SubcalendarID,
            DisplayColor,
            IconID
        )
        VALUES
        (
            @iEvtCalViewID,
            @sSubCalIDString,
            @sDisplayColorString,
            @sIconIDString
        )

    commit transaction
end