January 5, 2015 at 4:31 pm
Any tips on how to modify the code below would be greatly appreciated.
The transaction below takes as input two single valued variables @CopyFromVIEGuid and @CopyToVIEGuid.
I would like to modify the transaction so that instead of taking a single valued variable @CopyToVIEGuid, I could instead use a table variable containing several values and have the transaction run for each value.
IF (len(@CopyFromVIEGuid) > 0 AND len(@CopyToVIEGuid) > 0)
BEGIN
IF (NOT EXISTS(Select Null From [dbo].XdsInboundSecurity Where AssigningAuthorityVIEGuid=@CopyToVIEGuid) )
BEGIN
INSERT INTO [dbo].XdsInboundSecurity
(AssigningAuthorityVIEGuid, ClientCertificateThumbprint, ClientCertificateExpirationDate, IsActive,CreatedByVIEGuid,CreatedDate,ModifiedByVIEGuid,ModifiedDate,IsDeleted)
SELECT @CopyToVIEGuid, ClientCertificateThumbprint, ClientCertificateExpirationDate, IsActive,CreatedByVIEGuid,CreatedDate,ModifiedByVIEGuid,ModifiedDate,IsDeleted
FROM [dbo].XdsInboundSecurity
WHERE AssigningAuthorityVIEGuid= @CopyFromVIEGuid
IF @@ERROR <> 0 GOTO ERROR_HANDLER
END
ELSE
BEGIN
UPDATE [dbo].XdsInboundSecurity
SET ClientCertificateThumbprint = (Select ClientCertificateThumbprint From [dbo].XdsInboundSecurity Where AssigningAuthorityVIEGuid=@CopyFromVIEGuid)
WHERE AssigningAuthorityVIEGuid= @CopyToVIEGuid
IF @@ERROR <> 0 GOTO ERROR_HANDLER
END
END
January 5, 2015 at 5:13 pm
The process is outlined pretty clearly here in the Books Online. You need to create a TYPE the defines the table structure and then you can set the parameter for your stored procedure to that TYPE just like any other parameter, except, you have to define it as READONLY. But all that is well documented there in the books online.
Once you have the table, you just use it to JOIN to your other tables in place of using the WHERE clause when you used the variable value.
This can work very well.
But, be sure to test it with the largest expected data sets. The table valued parameter is based on the table variable which does not have statistics. So, large data sets, or any kind of filtering such as a WHERE clause, and even the JOIN criteria, could cause poor performance, depending on what you do with the code. But, relatively small data sets (<500 rows) with simple structures and the queries you already have should perform very well.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 6, 2015 at 3:40 pm
DECLARE @CopyParams TABLE (
CopyFromVIEGuid uniqueidentifier,
CopyToVIEGuid uniqueidentifier
)
UPDATE [dbo].XdsInboundSecurity
SET ClientCertificateThumbprint = (
Select Top (1) ClientCertificateThumbprint
From [dbo].XdsInboundSecurity
Where AssigningAuthorityVIEGuid=cp.CopyFromVIEGuid
)
FROM [dbo].XdsInboundSecurity
INNER JOIN @CopyParams cp ON
cp.CopyToVIEGuid = AssigningAuthorityVIEGuid
INSERT INTO [dbo].XdsInboundSecurity
(AssigningAuthorityVIEGuid, ClientCertificateThumbprint, ClientCertificateExpirationDate, IsActive,CreatedByVIEGuid,CreatedDate,ModifiedByVIEGuid,ModifiedDate,IsDeleted)
SELECT cp.CopyToVIEGuid, ClientCertificateThumbprint, ClientCertificateExpirationDate, IsActive,CreatedByVIEGuid,CreatedDate,ModifiedByVIEGuid,ModifiedDate,IsDeleted
FROM [dbo].XdsInboundSecurity
INNER JOIN @CopyParams cp ON
cp.CopyFromVIEGuid = AssigningAuthorityVIEGuid AND
NOT EXISTS(SELECT 1 FROM @CopyParams cp2 WHERE cp2.CopyToVIEGuid = AssigningAuthorityVIEGuid)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply