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