Using Silverlight in Enterprise: RAD of User Friendly Database Access

if exists (select	*
             from dbo.sysobjects
            where id = object_id(N'[dbo].[p_Framework_UserSettings_Update]')
              and objectproperty(id, N'IsProcedure') = 1)
  drop procedure [dbo].[p_Framework_UserSettings_Update]
go

/*
  Updates user settings table.
*/
create procedure dbo.p_Framework_UserSettings_Update
(
  @xmlInserted ntext,
  @xmlUpdated  ntext,
  @xmlDeleted  ntext
)
as
begin
  declare @docInserted   int,
          @docUpdated    int,
          @docDeleted    int,
          @RowsAffected  int,
          @UserSettingId int

  -----------------------------------------------------------------------------
  -- Process deleted records
  -----------------------------------------------------------------------------
  if @xmlDeleted is not null
  begin
    exec sp_xml_preparedocument @docDeleted output, @xmlDeleted
    
    declare c_deleted cursor local for
      select d.UserSettingId
        from openxml(@docDeleted, '/items/item', 1)
                with (UserSettingId int) d
                
    open c_deleted
    
    fetch next from c_deleted into @UserSettingId
    while @@FETCH_STATUS = 0
    begin
      delete from Framework_UserSettings
            where UserSettingId = @UserSettingId
              and not exists (select 1
                                from Framework_UserSettings us_c
                               where us_c.ParentId = @UserSettingId)
      fetch next from c_deleted into @UserSettingId
    end
    
    close c_deleted
    deallocate c_deleted
    
    exec sp_xml_removedocument @docDeleted
  end
  
  -----------------------------------------------------------------------------
  -- Process inserted records
  -----------------------------------------------------------------------------
  if @xmlInserted is not null
  begin
    create table #tabInserted
    (
      UserSettingId int, 
      OptionKey     nvarchar(250), 
      OptionType    nvarchar(30),
      ParentId      int,
      UserId        int,
      ApplicationCd nvarchar(50),
      Value         ntext
    )
    create index i_tabInserted_UserSettingId on #tabInserted (UserSettingId)
    
    exec sp_xml_preparedocument @docInserted output, @xmlInserted
    
    insert into #tabInserted
    (
      UserSettingId,
      OptionKey,
      OptionType,
      ParentId,
      UserId,
      ApplicationCd,
      Value
    )
    select i.UserSettingId,
           i.OptionKey,
           i.OptionType,
           i.ParentId,
           i.UserId,
           i.ApplicationCd,
           case
             when datalength(i.Value) > 0
             then i.Value
             else null
           end
      from openxml(@docInserted, '/items/item', 1)
              with (UserSettingId int, 
                    OptionKey     nvarchar(250), 
                    OptionType    nvarchar(30),
                    ParentId      int,
                    UserId        int,
                    ApplicationCd nvarchar(50),
                    Value         ntext) i
                    
    exec sp_xml_removedocument @docInserted
    
    create table #tabIdMap
    (
      ProposedId int,
      ActualId   int
    )
    create index i_tabIdMap_ProposedId on #tabIdMap (ProposedId)
    create index i_tabIdMap_ActualId on #tabIdMap (ActualId)
    
    -- If record with required alternative key already exists,
    -- create proposed-to-actual ID mapping
    set @RowsAffected = 1
    
    while @RowsAffected > 0
    begin
      set @RowsAffected = 0
      
      insert into #tabIdMap
      (
        ProposedId,
        ActualId
      )
      select i.UserSettingId,
             us.UserSettingId
        from #tabInserted i
       inner join Framework_UserSettings us
               on us.OptionKey = i.OptionKey
              and us.OptionType = i.OptionType
              and us.UserId = i.UserId
              and us.ApplicationCd = i.ApplicationCd
              and us.ParentId is null
       where i.ParentId is null
         and not exists (select 1
                           from #tabIdMap m
                          where m.ProposedId = i.UserSettingId)
      set @RowsAffected = @RowsAffected + isnull(@@ROWCOUNT, 0)
      
      insert into #tabIdMap
      (
        ProposedId,
        ActualId
      )
      select i.UserSettingId,
             us.UserSettingId
        from #tabInserted i
        left join #tabIdMap pm
          on i.ParentId = pm.ProposedId
       inner join Framework_UserSettings us
               on us.OptionKey = i.OptionKey
              and us.OptionType = i.OptionType
              and us.UserId = i.UserId
              and us.ApplicationCd = i.ApplicationCd
              and us.ParentId = isnull(pm.ActualId, i.ParentId)
       where i.ParentId is not null
         and not exists (select 1
                           from #tabIdMap m
                          where m.ProposedId = i.UserSettingId)
      set @RowsAffected = @RowsAffected + isnull(@@ROWCOUNT, 0)
    end
    
    -- Insert records with non-existing alternative key
    insert into Framework_UserSettings
    (
      UserSettingId,
      OptionKey,
      OptionType,
      ParentId,
      UserId,
      ApplicationCd,
      Value
    )
    select i.UserSettingId,
           i.OptionKey,
           i.OptionType,
           isnull(pm.ActualId, i.ParentId),
           i.UserId,
           i.ApplicationCd,
           i.Value
      from #tabInserted i
      left join #tabIdMap pm
        on i.ParentId = pm.ProposedId
     where not exists (select 1
                         from #tabIdMap m
                        where m.ProposedId = i.UserSettingId)
                        
    -- Update records with existing alternative key                      
    update Framework_UserSettings
       set Value = i.Value
      from Framework_UserSettings us
     inner join #tabIdMap m
        on us.UserSettingId = m.ActualId
     inner join #tabInserted i
        on m.ProposedId = i.UserSettingId
        
    drop table #tabInserted
    drop table #tabIdMap
  end

  -----------------------------------------------------------------------------
  -- Process updated records     
  -----------------------------------------------------------------------------
  if @xmlUpdated is not null
  begin
    create table #tabUpdated
    (
      UserSettingId int,
      Value         ntext
    )
    create index i_tabUpdated_UserSettingId on #tabUpdated (UserSettingId)
    
    exec sp_xml_preparedocument @docUpdated output, @xmlUpdated
    
    insert into #tabUpdated
    (
      UserSettingId,
      Value
    )
    select u.UserSettingId,
           case
             when datalength(u.Value) > 0
             then u.Value
             else null
           end
      from openxml(@docUpdated, '/items/item', 1)
              with (UserSettingId int, Value ntext) u
                   
    exec sp_xml_removedocument @docUpdated
    
    update Framework_UserSettings
       set Value = u.Value
      from Framework_UserSettings us
     inner join #tabUpdated u
        on us.UserSettingId = u.UserSettingId
        
    drop table #tabUpdated
  end
end
go