November 13, 2008 at 7:42 am
I have a table in which I need to insert multiple fields, including two documents of any size that will be encrypted in sql server 2005. I have come to a solution that requires two temporary tables and the main table. My problem is is that when I combine the two temporary tables into the main table, the row are either duplicated or disjointed. I have the same fields in the temp tables as I do the main table, only difference is is that each temp table contains one document each. Is there a better way??? Thanks to those who respond. See the procedure below
CREATE PROCEDURE AddAffiliate
-- Add the parameters for the stored procedure here
@FullLegalName nvarchar(150),
@TypeOfEntity nvarchar(150),
@LicenseNumber nvarchar(50),
@StateLicensed nvarchar(50),
@website nvarchar(200),
@TaxID nvarchar(50),
@Address1 nvarchar(50),
@Address2 nvarchar(50),
@City nvarchar(50),
@State nvarchar(25),
@ZipCode nvarchar(10),
@ContactPerson nvarchar(50),
@EMail nvarchar(150),
@PhoneNumber nvarchar(15),
@FaxNumber nvarchar(15),
@CopyOfLicensevarbinary(max),
@CopyOfW9varbinary(max),
@LoginID nvarchar(50),
@Password nvarchar(50)
AS
BEGIN
declare @idx int
declare @licLength int
declare @w9Length int
declare @blockSize int
declare @blockNum int
declare @block varbinary(7500)
declare @keyGuid uniqueidentifier
OPEN SYMMETRIC KEY AKey
DECRYPTION BY CERTIFICATE HappyCert WITH PASSWORD = '#####';
set @keyGuid = Key_GUID('AKey')
set @blockSize = 7600
BEGIN
CREATE TABLE #EncyptedLicenseData
(
FullLegalName nvarchar(150),
TypeOfEntity nvarchar(150),
LicenseNumber nvarchar(150),
StateLicensed nvarchar(150),
Website nvarchar(200),
TaxID nvarchar(150),
Address1 nvarchar(150),
Address2 nvarchar(150),
City nvarchar(150),
State nvarchar(25),
ZipCode nvarchar(10),
ContactPerson nvarchar(150),
EMail nvarchar(150),
PhoneNumber nvarchar(15),
FaxNumber nvarchar(15),
CopyOfLicense varbinary(max),
LicenseBlockNum int,
CopyOfW9 varbinary(max),
W9BlockNum int,
LoginID nvarchar(50),
Password nvarchar(50)
);
set @blockNum = 1
set @idx = 1
set @licLength = datalength(@CopyOfLicense)
WHILE @idx < @licLength
BEGIN
set @block = EncryptByKey(@keyGuid, SUBSTRING(@CopyOfLicense, @idx, @blockSize))
insert into #EncyptedLicenseData ([FullLegalName], [TypeOfEntity], [LicenseNumber], [StateLicensed],
[Website], [TaxID], [Address1], [Address2], [City], [State], [ZipCode],
[ContactPerson], [EMail],[PhoneNumber], [FaxNumber],[CopyOfLicense],
[LicenseBlockNum], [CopyOfW9], [W9BlockNum], [LoginID], [Password])
values(@FullLegalName, @TypeOfEntity, @LicenseNumber, @StateLicensed,
@website, @TaxID, @Address1, @Address2, @City, @State, @ZipCode,
@ContactPerson, @EMail,@PhoneNumber, @FaxNumber,
@block, @blockNum,
NULL, NULL,
@LoginID, @Password);
set @idx = @idx + @blockSize
set @blockNum = @blockNum + 1
END
END
BEGIN
CREATE TABLE #EncryptedW9Data
(
FullLegalName nvarchar(150),
TypeOfEntity nvarchar(150),
LicenseNumber nvarchar(150),
StateLicensed nvarchar(150),
Website nvarchar(200),
TaxID nvarchar(150),
Address1 nvarchar(150),
Address2 nvarchar(150),
City nvarchar(150),
State nvarchar(25),
ZipCode nvarchar(10),
ContactPerson nvarchar(150),
EMail nvarchar(150),
PhoneNumber nvarchar(15),
FaxNumber nvarchar(15),
CopyOfLicense varbinary(max),
LicenseBlockNum int,
CopyOfW9 varbinary(max),
W9BlockNum int,
LoginID nvarchar(50),
Password nvarchar(50)
);
set @blockNum = 1
set @idx = 1
set @w9Length = datalength(@CopyOfW9)
WHILE @idx < @w9Length
BEGIN
set @block = EncryptByKey(@keyGuid, SUBSTRING(@CopyOfW9, @idx, @blockSize))
insert into #EncryptedW9Data ([FullLegalName], [TypeOfEntity], [LicenseNumber], [StateLicensed],
[Website], [TaxID], [Address1], [Address2], [City], [State], [ZipCode],
[ContactPerson], [EMail],[PhoneNumber], [FaxNumber],[CopyOfLicense],
[LicenseBlockNum], [CopyOfW9], [W9BlockNum], [LoginID], [Password])
values(@FullLegalName, @TypeOfEntity, @LicenseNumber, @StateLicensed,
@website, @TaxID, @Address1, @Address2, @City, @State, @ZipCode,
@ContactPerson, @EMail,@PhoneNumber, @FaxNumber,
NULL, NULL,
@block, @blockNum,
@LoginID, @Password);
set @idx = @idx + @blockSize
set @blockNum = @blockNum + 1
END
END
BEGIN
SET NOCOUNT ON
INSERT INTO dbo.Affiliate([FullLegalName], [TypeOfEntity], [LicenseNumber], [StateLicensed],
[Website], [TaxID], [Address1], [Address2], [City], [State], [ZipCode],
[ContactPerson], [EMail],[PhoneNumber], [FaxNumber],[CopyOfLicense],
[LicenseBlockNum], [CopyOfW9], [W9BlockNum], [LoginID], [Password])
SELECT[#EncyptedLicenseData].[FullLegalName],
[#EncyptedLicenseData].[TypeOfEntity],
[#EncyptedLicenseData].[LicenseNumber],
[#EncyptedLicenseData].[StateLicensed],
[#EncyptedLicenseData].[Website],
[#EncyptedLicenseData].[TaxID],
[#EncyptedLicenseData].[Address1],
[#EncyptedLicenseData].[Address2],
[#EncyptedLicenseData].[City],
[#EncyptedLicenseData].[State],
[#EncyptedLicenseData].[ZipCode],
[#EncyptedLicenseData].[ContactPerson],
[#EncyptedLicenseData].[EMail],
[#EncyptedLicenseData].[PhoneNumber],
[#EncyptedLicenseData].[FaxNumber],
ISNULL([#EncyptedLicenseData].[CopyOfLicense],[#EncryptedW9Data].[CopyOfLicense]) ,
ISNULL([#EncyptedLicenseData].[LicenseBlockNum],[#EncryptedW9Data].[LicenseBlockNum]),
ISNULL([#EncyptedLicenseData].[CopyOfW9],[#EncryptedW9Data].[CopyOfW9]),
ISNULL([#EncyptedLicenseData].[W9BlockNum],[#EncryptedW9Data].[W9BlockNum]),
[#EncyptedLicenseData].[LoginID],
[#EncyptedLicenseData].[Password]
FROM [#EncyptedLicenseData]
INNER JOIN [#EncryptedW9Data] ON
[#EncyptedLicenseData].[FullLegalName] = [#EncryptedW9Data].[FullLegalName]
END
CLOSE SYMMETRIC KEY AKey;
END
November 13, 2008 at 9:29 am
SOLVED!!!!
INSERT INTO dbo.Affiliate([FullLegalName], [TypeOfEntity], [LicenseNumber], [StateLicensed],
[Website], [TaxID], [Address1], [Address2], [City], [State], [ZipCode],
[ContactPerson], [EMail],[PhoneNumber], [FaxNumber],[CopyOfLicense],
[LicenseBlockNum], [CopyOfW9], [W9BlockNum], [LoginID], [Password])
SELECTISNULL(ELD.[FullLegalName], EWD.[FullLegalName]),
ISNULL(ELD.[TypeOfEntity], EWD.[TypeOfEntity]),
ISNULL(ELD.[LicenseNumber], EWD.[LicenseNumber]),
ISNULL(ELD.[StateLicensed],EWD.[StateLicensed]),
ISNULL(ELD.[Website], EWD.[Website]),
ISNULL(ELD.[TaxID], EWD.[TaxID]),
ISNULL(ELD.[Address1], EWD.[Address1]),
ISNULL(ELD.[Address2], EWD.[Address2]),
ISNULL(ELD.[City], EWD.[City]),
ISNULL(ELD.[State], EWD.[State]),
ISNULL(ELD.[ZipCode],EWD.[ZipCode]),
ISNULL(ELD.[ContactPerson], EWD.[ContactPerson]),
ISNULL(ELD.[EMail],EWD.[EMail]),
ISNULL(ELD.[PhoneNumber], EWD.[PhoneNumber]),
ISNULL(ELD.[FaxNumber],EWD.[FaxNumber]),
ELD.[CopyOfLicense],
ELD.[LicenseBlockNum],
EWD.[CopyOfW9],
EWD.[W9BlockNum],
ISNULL(ELD.[LoginID], EWD.[LoginID]),
ISNULL(ELD.[Password], EWD.[Password])
FROM [#EncyptedLicenseData] ELD
FULL OUTER JOIN [#EncryptedW9Data] AS EWD ON
ELD.[FullLegalName] = EWD.[FullLegalName]
AND ISNULL(ELD.[LicenseBlockNum], EWD.[W9BlockNum]) = ISNULL(EWD.[W9BlockNum], ELD.[LicenseBlockNum])
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply