Since I obviously don't have access to your tables, I'd double check this does what you anticipate, but here's how I'd rewrite the script to do this as set based operations, and the only thing you need to do is populate the initial CopyTBToAA and CopyTBFromAA columns in the @inputMap table variable I declared.
I wasn't entirely clear whether your question pertained primarily to rewriting the procedure to be set based (which is what the following code does) or about doing the string splitting. If you're more wondering about the latter, let me know and I can try to put some more detail around how to pass in a serialized string to a procedure and get to where this script picks up.
use tempdb
go
set nocount on
set xact_abort on
go
/**********************
How this section is derived isn't really important.
You can use a split function, xml, or a table variable.
*********************/
declare @inputMap table
(
CopyTBToAA varchar(50) primary key clustered,
ToVIEGuid varchar(50) null,
CopyTBFromAA varchar(50) not null,
FromVIEGuid varchar(50) null
)
--Give it all the to/from mappings you want
insert into @inputMap (CopyTBToAA, CopyTBFromAA)
select 'Name1 IP/OP', 'Name2 IP/OP' union all
select 'Name3 IP/OP', 'Name7 IP/OP'
/*****************
Get the VIE GUIDs associated with both the from and to names
*****************/
--"TO" first
update im
set ToVIEGUID = VIEGuid
from @inputMap im
inner join Healinx.dbo.AssigningAuthority a
on im.CopyTBToAA = a.Name
--"FROM" second
update im
set ToVIEGUID = VIEGuid
from @inputMap im
inner join Healinx.dbo.AssigningAuthority a
on im.CopyTBToAA = a.Name
/**************
Error checking. I'm making assumptions about how you want to handle this,
so please review how you want this to fucntion in the event one of your values failed to get a guid.
**************/
if exists (select 1 from @inputMap where ToVIEGuid is null or FromVIEGuid is null)
begin
raiserror('A guid lookup failed', 16, 1)
return
end
/*************
Upsert data
*************/
begin try
begin tran
--Update any existing records with the "TO" guid with the "FROM" values
update xis
set ClientCertificateThumbprint = src.ClientCertificateThumbprint
from Interop.dbo.XdsInboundSecurity xis
inner join @inputMap im --Update records in xis which match the FromVIEGuid
on xis.AssigningAuthorityVIEGuid = im.FromVIEGuid
inner join Interop.dbo.XdsInboundSecurity src --Pull values based on ToVIEGuid
on im.FromVIEGuid = src.AssigningAuthorityVIEGuid
--Insert any records that need to be created
insert into interop.dbo.XdsInboundSecurity
(
AssigningAuthorityVIEGuid,
ClientCertificateThumbprint,
ClientCertificateExpirationDate,
IsActive,
CreatedByVIEGuid,
CreatedDate,
ModifiedByVIEGuid,
ModifiedDate,
IsDeleted
)
select
im.ToVIEGUID, --Use the "TO" guid as the primary key
src.ClientCertificateThumbprint, --Everything else comes from the "FROM" values
src.ClientCertificateExpirationDate,
src.IsActive,
src.CreatedByVIEGuid,
src.CreatedDate,
src.ModifiedByVIEGuid,
src.ModifiedDate,
src.IsDeleted
from interop.dbo.XdsInboundSecurity src
inner join @inputMap im
on src.AssigningAuthorityVIEGuid = im.FromVIEGuid
left outer join interop.dbo.XdsInboundSecurity tar
on im.ToVIEGuid = tar.AssigningAuthorityVIEGuid --Left outer join in combination with the null check on the next line ensures you wont insert records that already exist.
where tar.AssigningAuthorityVIEGuid is null
commit tran
end try
begin catch
raiserror('Error occured; the changes have been rolled back', 16, 1);
if xact_state() != 0
rollback tran
return
end catch