October 17, 2014 at 11:01 am
Hi, I have the code below which updates the field values for records with a specific field value (@CopyTBToAA ) with the field values from records with another specific value (@CopyTBFromAA ).
The problem I have is that I would like to be able to put in a whole list of values for (@CopyTBToAA ) and have all the records with that (@CopyTBToAA ) value updated with the field values from the (@CopyTBFromAA ) record.
If someone can give me some pointers on how to change my code below to do that, I'd be grateful. I'm a little new to dynamic sql.
Code:
SET XACT_ABORT ON -- Ensures that transactions are rolled back automatically on errors
DECLARE @Dry_Run int = 1 -- 0 to commit, 1 to rollback
DECLARE @CopyTBFromAA varchar(50)
DECLARE @CopyTBToAA varchar(50)
SET @CopyTBFromAA='Name1 IP/OP'
SET @CopyTBToAA='NAME2 IP/OP'
Declare @CopyFromVIEGuid varchar(50)
Declare @CopyToVIEGuid varchar(50)
Select @CopyFromVIEGuid = VIEGuid From [HEALINX].[dbo].AssigningAuthority Where Name=@CopyTBFromAA
Select @CopyToVIEGuid = VIEGuid From [HEALINX].[dbo].AssigningAuthority Where Name=@CopyTBToAA
BEGIN TRANSACTION
IF (len(@CopyFromVIEGuid) > 0 AND len(@CopyToVIEGuid) > 0)
BEGIN
IF (NOT EXISTS(Select Null From [INTEROP].[dbo].XdsInboundSecurity Where AssigningAuthorityVIEGuid=@CopyToVIEGuid) )
BEGIN
INSERT INTO [INTEROP].[dbo].XdsInboundSecurity
(AssigningAuthorityVIEGuid, ClientCertificateThumbprint, ClientCertificateExpirationDate, IsActive,CreatedByVIEGuid,CreatedDate,ModifiedByVIEGuid,ModifiedDate,IsDeleted)
SELECT @CopyToVIEGuid, ClientCertificateThumbprint, ClientCertificateExpirationDate, IsActive,CreatedByVIEGuid,CreatedDate,ModifiedByVIEGuid,ModifiedDate,IsDeleted
FROM [INTEROP].[dbo].XdsInboundSecurity
WHERE AssigningAuthorityVIEGuid= @CopyFromVIEGuid
IF @@ERROR <> 0 GOTO ERROR_HANDLER
END
ELSE
BEGIN
UPDATE [INTEROP].[dbo].XdsInboundSecurity
SET ClientCertificateThumbprint = (Select ClientCertificateThumbprint From [INTEROP].[dbo].XdsInboundSecurity Where AssigningAuthorityVIEGuid=@CopyFromVIEGuid)
WHERE AssigningAuthorityVIEGuid= @CopyToVIEGuid
IF @@ERROR <> 0 GOTO ERROR_HANDLER
END
END
ELSE
BEGIN
PRINT 'Given Assigning Authority information not found in the system'
GOTO AFTER_VALUES
END
IF @Dry_Run = 1 GOTO DRY_RUN
COMMIT TRANSACTION
SELECT 'Success'
GOTO AFTER_VALUES
ERROR_HANDLER:
ROLLBACK TRANSACTION
PRINT 'An error has occured; the changes have been rolled back.'
GOTO AFTER_VALUES
DRY_RUN:
SELECT * FROM [INTEROP].[dbo].XdsInboundSecurity Where AssigningAuthorityVIEGuid=@CopyToVIEGuid
ROLLBACK TRANSACTION
SELECT 'Dry Run'
GOTO AFTER_VALUES
AFTER_VALUES:
SELECT 'AFTER TRANSACTION'
SELECT * FROM [INTEROP].[dbo].XdsInboundSecurity Where AssigningAuthorityVIEGuid=@CopyToVIEGuid
SET XACT_ABORT OFF -- Reverting back to the default
October 17, 2014 at 3:16 pm
Three options come to mind (in my personal order of preference):
1) A delimited string of to and from values. This requires that you have a string split function built on your server. Look at this article if you're interested: http://www.sqlservercentral.com/articles/Tally+Table/72993/
You could build a string that looks something like this
select *
from dbo.splitFunction('From1|To1@@From2|To2', '@@')
and then use the CHARINDEX function to further split each parsed value into the FROM and TO Values.
2) Use XML. This has the advantage that there are already built in XML functions and you don't need to mess with a split function if you're averse to that for some reason. You also need to have some background in XML though.
declare @xml xml = '<nodes><row FromValue="from1" ToValue="to1"/><row FromValue="from2" ToValue="to2"/></nodes>'
select
t.c.value('@FromValue', 'varchar(30)'),
t.c.value('@ToValue', 'varchar(30)')
from @xml.nodes('nodes/row') as t(c)
3) Use a table-valued parameter. This is my least favorite option because there's a lot of baggage that comes along with using TVPs. But they're still an option.
To go this route, you'd need to define a custom table type, insert the values you want into it, and then pass that custom type into your procedure
create type dbo.ToFromTableType as table
(
FromValue varchar(30),
ToValue varchar(30)
)
create proc dbo.tvpInsert
@ToFromTable ToFromTableType readonly
as
...--Proc code
The upshot of any of these methods is to get a table of the to/from values you want to effect all at once. Once you have a table built up with multiple rows of to/from values, you can do an update against all of them at once.
October 22, 2014 at 6:27 pm
Thank you for getting back to me on this.
I made a slight change to the code below and added a while loop to split the comma separated list up, and pass each value to the transaction below it. Would something that simple work?
SET XACT_ABORT ON -- Ensures that transactions are rolled back automatically on errors
DECLARE @Dry_Run int = 1 -- 0 to commit, 1 to rollback
DECLARE @CopyTBFromAA varchar(50)
DECLARE @CopyTBToAA varchar(50)
SET @CopyTBFromAA='Name1 IP/OP'
--Added code to split comma separated list of AssigningAuthorities, and pass one AssigningAuthority at a time to old code.
DECLARE @comma VARCHAR(MAX) = 'NAME1,NAME2,NAME3,'
DECLARE @begin INT = 0, @count INT = 0, @max-2 INT
SELECT @max-2 = LEN(@comma) - LEN(REPLACE(@comma,',','')
WHILE @count < @max-2
BEGIN
SET @CopyTBToAA=(SELECT SUBSTRING(@comma,@begin,CHARINDEX(',',@comma,@begin+1)-@begin))--Copy thumbprint to AssigningAuthority
--SELECT (CHARINDEX(',',@comma,@begin+1)+1)
SET @begin = (CHARINDEX(',',@comma,@begin+1)+1)
SET @count = @count + 1
END
--End
Declare @CopyFromVIEGuid varchar(50)
Declare @CopyToVIEGuid varchar(50)
Select @CopyFromVIEGuid = VIEGuid From [HEALINX].[dbo].AssigningAuthority Where Name=@CopyTBFromAA
Select @CopyToVIEGuid = VIEGuid From [HEALINX].[dbo].AssigningAuthority Where Name=@CopyTBToAA
BEGIN TRANSACTION
IF (len(@CopyFromVIEGuid) > 0 AND len(@CopyToVIEGuid) > 0)
BEGIN
IF (NOT EXISTS(Select Null From [INTEROP].[dbo].XdsInboundSecurity Where AssigningAuthorityVIEGuid=@CopyToVIEGuid) )
BEGIN
INSERT INTO [INTEROP].[dbo].XdsInboundSecurity
(AssigningAuthorityVIEGuid, ClientCertificateThumbprint, ClientCertificateExpirationDate, IsActive,CreatedByVIEGuid,CreatedDate,ModifiedByVIEGuid,ModifiedDate,IsDeleted)
SELECT @CopyToVIEGuid, ClientCertificateThumbprint, ClientCertificateExpirationDate, IsActive,CreatedByVIEGuid,CreatedDate,ModifiedByVIEGuid,ModifiedDate,IsDeleted
FROM [INTEROP].[dbo].XdsInboundSecurity
WHERE AssigningAuthorityVIEGuid= @CopyFromVIEGuid
IF @@ERROR <> 0 GOTO ERROR_HANDLER
END
ELSE
BEGIN
UPDATE [INTEROP].[dbo].XdsInboundSecurity
SET ClientCertificateThumbprint = (Select ClientCertificateThumbprint From [INTEROP].[dbo].XdsInboundSecurity Where AssigningAuthorityVIEGuid=@CopyFromVIEGuid)
WHERE AssigningAuthorityVIEGuid= @CopyToVIEGuid
IF @@ERROR <> 0 GOTO ERROR_HANDLER
END
END
ELSE
BEGIN
PRINT 'Given Assigning Authority information not found in the system'
GOTO AFTER_VALUES
END
IF @Dry_Run = 1 GOTO DRY_RUN
COMMIT TRANSACTION
SELECT 'Success'
GOTO AFTER_VALUES
ERROR_HANDLER:
ROLLBACK TRANSACTION
PRINT 'An error has occured; the changes have been rolled back.'
GOTO AFTER_VALUES
DRY_RUN:
SELECT * FROM [INTEROP].[dbo].XdsInboundSecurity Where AssigningAuthorityVIEGuid=@CopyToVIEGuid
ROLLBACK TRANSACTION
SELECT 'Dry Run'
GOTO AFTER_VALUES
AFTER_VALUES:
SELECT 'AFTER TRANSACTION'
SELECT * FROM [INTEROP].[dbo].XdsInboundSecurity Where AssigningAuthorityVIEGuid=@CopyToVIEGuid
SET XACT_ABORT OFF -- Reverting back to the default
October 22, 2014 at 9:04 pm
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
October 22, 2014 at 11:21 pm
Hi and thanks again for getting back to me so quickly.
Yes it's the latter, the string splitting and then passing the values to the transaction that I'm primarily interested in. Would the simple loop that I added work?
October 23, 2014 at 9:32 am
If you want to go the string split route, you might as well go all out. If you want to read through it, I'd check out this article, but the following code is just about the best split function around:
http://www.sqlservercentral.com/articles/Tally+Table/72993/
1) You'll need a tally (or numbers) table. If you don't have one in your DB yet, use this opportunity to make one. They come in handy all the time. This is NOT the fastest way
create table dbo.numbers (num int primary key clustered)
insert into dbo.numbers (num) select top 100000 row_number() over (order by (select null)) from sys.objects a, sys.objects b, sys.objects c
2) Built the following function. Again, if you want to understand what it's doing, read the article above. Otherwise just take my word for it that this performs very well.
create function [dbo].[ParseString] (@String varchar(max), @Delimiter varchar(10))
returns table
as
return (
select Ident, StringValue from
(
select Num as Ident,
case
when datalength(@delimiter) = 0 or @delimiter is null
then substring(@string, num, 1)
else
ltrim(rtrim(substring(@String,
case
when (Num = 1 and substring(@String, num, datalength(@delimiter)) <> @delimiter) then 1
else Num + datalength(@delimiter)
end,
case charindex(@Delimiter, @String, Num + datalength(@delimiter))
when 0 then len(@String) - Num + datalength(@delimiter)
else charindex(@Delimiter, @String, Num + datalength(@delimiter)) - Num -
case
when Num > 1 or (Num = 1 and substring(@String, num, datalength(@delimiter)) = @delimiter)
then datalength(@delimiter)
else 0
end
end
)))
end as StringValue
from dbo.Numbers
where Num <= len(@String)
and (
substring(@String, Num, datalength(isnull(@delimiter, ''))) = @Delimiter
or Num = 1
or datalength(isnull(@delimiter, '')) = 0
)
) R where datalength(StringValue) <> 0
)
3) Now you can start splitting values. This function will split a string and return an ident column (indicating the order in which the element existed in your original delimited string) and a StringValue, aka the element value itself. Here's a sample call:
select ident, stringValue
from dbo.ParseString('Name1,Name2,Name3', ',') --second parameter is the character(s) to split on
4) Now lets look at how to use this function to populate what your procedure needs. In your post, you gave it a single FromAA value and a list of ToAA values. Have your proc take those as inputs, and then populate the @inputMap table I mentioned like this:
declare
@CopyTBFromAA varchar(50) = 'Name1 IP/OP',
@DelimitedToNames varchar(8000) = 'NAME1,NAME2,NAME3'
declare @inputMap table
(
CopyTBToAA varchar(50) primary key clustered,
ToVIEGuid varchar(50) null,
CopyTBFromAA varchar(50) not null,
FromVIEGuid varchar(50) null
)
insert into @inputMap
(
CopyTBToAA,
CopyTBFromAA
)
select
CopyTBToAA = stringValue,
CopyTBFromAA = @CopyTBFromAA
from dbo.parseString(@DelimitedToNames, ',')
select *
from @InputMap
November 11, 2014 at 12:36 pm
Hi sorry for the delay in updating on this issue, and thank you again for all your help.
I found my db had a string split function. So I modified my code to parse the string, and load the values into a table variable @TBToAA.
My complete updated script is below, but in particular I'm curious if part where I look for the Name in the @TBToAA table variable in order to update multiple assigning authorities will work the way I think it should. That piece is in the example below.
example:
Select @CopyToVIEGuid = VIEGuid From [Healinx].[dbo].AssigningAuthority Where Name in (select * from @TBToAA) --Replaced single value @CopyTBToAA with @TBToAA Table Variable with multiple AssigningAuthority values
Can you tell if this will cause the script to insert records for all the Name in @TBToAA, or will it just do it for one Name in @TBToAA and quit?
Complete Script:
SET XACT_ABORT ON -- Ensures that transactions are rolled back automatically on errors
--Declare and set variables to copy ClientCertificateThumbprint for AssigningAuthority
DECLARE @Dry_Run int = 1 -- 0 to commit, 1 to rollback
DECLARE @CopyTBFromAA varchar(50)
--Added @CopyTBToAAList to hold list of AssigningAuthorities to copy thumbprint to
DECLARE @CopyTBToAAList varchar(MAX)
SET @CopyTBFromAA='Name1' --Test Copy thumbprint from AssigningAuthority
SET @CopyTBToAAList = 'Name1,Name2'--Test Copy thumbprint to AssigningAuthorities
--Added Table variable to hold individual CopyTBToAA values
DECLARE @TBToAA TABLE (CopyTBToAA varchar(MAX))
--Added string split function to parse @CopyTBToAAList and insert values into @TBToAA table variable
BEGIN
DECLARE @CopyTBToAAListCount int
INSERT INTO @TBToAA
SELECT DISTINCT s.Token FROM util.[SplitDelimitedString](@CopyTBToAAList, ',') s order by s.Token
END
--Select AssigningAuthorityVIEGuid
Declare @CopyFromVIEGuid varchar(50)
Declare @CopyToVIEGuid varchar(50)
Select @CopyFromVIEGuid = VIEGuid From [Healinx].[dbo].AssigningAuthority Where Name=@CopyTBFromAA
Select @CopyToVIEGuid = VIEGuid From [Healinx].[dbo].AssigningAuthority Where Name in (select * from @TBToAA) --Replaced single value @CopyTBToAA with @TBToAA Table Variable with multiple AssigningAuthority values
BEGIN TRANSACTION
IF (len(@CopyFromVIEGuid) > 0 AND len(@CopyToVIEGuid) > 0)
BEGIN
IF (NOT EXISTS(Select Null From [INTEROP].[dbo].XdsInboundSecurity Where AssigningAuthorityVIEGuid=@CopyToVIEGuid) )
BEGIN
INSERT INTO [INTEROP].[dbo].XdsInboundSecurity
(AssigningAuthorityVIEGuid, ClientCertificateThumbprint, ClientCertificateExpirationDate, IsActive,CreatedByVIEGuid,CreatedDate,ModifiedByVIEGuid,ModifiedDate,IsDeleted)
SELECT @CopyToVIEGuid, ClientCertificateThumbprint, ClientCertificateExpirationDate, IsActive,CreatedByVIEGuid,CreatedDate,ModifiedByVIEGuid,ModifiedDate,IsDeleted
FROM [INTEROP].[dbo].XdsInboundSecurity
WHERE AssigningAuthorityVIEGuid= @CopyFromVIEGuid
IF @@ERROR <> 0 GOTO ERROR_HANDLER
END
ELSE
BEGIN
UPDATE [INTEROP].[dbo].XdsInboundSecurity
SET ClientCertificateThumbprint = (Select ClientCertificateThumbprint From [INTEROP].[dbo].XdsInboundSecurity Where AssigningAuthorityVIEGuid=@CopyFromVIEGuid)
WHERE AssigningAuthorityVIEGuid= @CopyToVIEGuid
IF @@ERROR <> 0 GOTO ERROR_HANDLER
END
END
ELSE
BEGIN
PRINT 'Given Assigning Authority information not found in the system'
GOTO AFTER_VALUES
END
IF @Dry_Run = 1 GOTO DRY_RUN
COMMIT TRANSACTION
SELECT 'Success'
GOTO AFTER_VALUES
ERROR_HANDLER:
ROLLBACK TRANSACTION
PRINT 'An error has occured; the changes have been rolled back.'
GOTO AFTER_VALUES
DRY_RUN:
SELECT * FROM [INTEROP].[dbo].XdsInboundSecurity Where AssigningAuthorityVIEGuid=@CopyToVIEGuid
ROLLBACK TRANSACTION
SELECT 'Dry Run'
GOTO AFTER_VALUES
AFTER_VALUES:
SELECT 'AFTER TRANSACTION'
SELECT * FROM [INTEROP].[dbo].XdsInboundSecurity Where AssigningAuthorityVIEGuid=@CopyToVIEGuid
SET XACT_ABORT OFF -- Reverting back to the default
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply