在 SQL Server 2014 中执行批量插入和解密对称密钥

Question

我有一个存储过程，如果解密的值与另一个表中的值匹配，则执行对称密钥解密，然后插入到表中。

我希望修改该过程以执行批量插入，而不是打开和关闭每行上的数据库连接以执行该过程。

我的想法是对整个列(EmployeeSeed.SEED(执行解密，然后在插入语句内对Employee_FN执行连接。

有没有人对如何将其转换为批量插入有任何建议？

这是我的代码：

USE [Tracker]
GO
/****** Object:  StoredProcedure [dbo].[VACATION]   Script Date: 7/20/2017 3:10:18 PM ******/
SET ANSI_NULLS ON -- return no rows if null value is present
GO
SET QUOTED_IDENTIFIER ON -- define literals to be delimited by '' and identifiers by ""
GO
ALTER PROCEDURE [dbo].[VACATION]
@ID nvarchar(40),
@HOURS float,
@DESC nvarchar(max),
@TYPE nvarchar(50)
AS
OPEN SYMMETRIC KEY EmployeeCert_Key
DECRYPTION BY CERTIFICATE EmployeeCert
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @seed VARBINARY(16)
DECLARE @EMPLOYEE_FN nvarchar(20)

--Select Employee FN based on Decrypted ID
SET @EMPLOYEE_FN =
(
SELECT EMPLOYEE.[EMPLOYEE_FN]
FROM Employee
JOIN EmployeeSeed
ON Employee.EMPLOYEE_FN = EMPLOYEESEED.EMPLOYEE_FN
WHERE (CONVERT(nvarchar,                        -- Convert from binary to NVARCHAR
DECRYPTBYKEY(ID, 1,             -- Designate column (1 is same as encrypted)
HASHBYTES('SHA1', EmployeeSeed.SEED))) -- SHA1 seed
= @ID)
)
--insert into table
IF @EMPLOYEE_FN is not null
BEGIN
INSERT INTO [dbo].VACATION_BENEFIT
([EMPLOYEE_FN]
,[HOURS]
,[TYPE]
,[DESC])
VALUES
(@EMPLOYEE_FN,
@HOURS,
@TYPE,
@DESC)
END
CLOSE SYMMETRIC KEY EmployeeCert_Key
END

Answer 1

您可以创建用户定义的类型表来传递批量数据。

CREATE TYPE UT_VacationDetails AS TABLE  
(  
ID  nvarchar(40),  
[HOURS] float, 
[DESC] nvarchar(max),  
[TYPE] varchar(50)  
)  
GO
USE [Tracker]
USE [Tracker]
GO
/****** Object:  StoredProcedure [dbo].[VACATION]   Script Date: 
7/20/2017 3:10:18 PM ******/
SET ANSI_NULLS ON -- return no rows if null value is present
GO
SET QUOTED_IDENTIFIER ON -- define literals to be delimited by '' and 
identifiers by ""
GO

ALTER PROCEDURE [dbo].[VACATION]
-- Add the parameters for the stored procedure here
@UT_details UT_VacationDetails readonly
AS
OPEN SYMMETRIC KEY EmployeeCert_Key
DECRYPTION BY CERTIFICATE EmployeeCert
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @seed VARBINARY(16)
DECLARE @EMPLOYEE_FN nvarchar(20)
INSERT INTO [dbo].VACATION_BENEFIT ([EMPLOYEE_FN]
,[HOURS]
,[TYPE]
,[DESC])
SELECT EMPLOYEE.[EMPLOYEE_FN],T.[HOURS], T.[Type],T.[DESC]
FROM Employee
JOIN EmployeeSeed
ON Employee.EMPLOYEE_FN = EMPLOYEESEED.EMPLOYEE_FN
INNER JOIN @UT_details t On t.ID=  (CONVERT(nvarchar, DECRYPTBYKEY(ID, 1, 
HASHBYTES('SHA1', EmployeeSeed.SEED)))

CLOSE SYMMETRIC KEY EmployeeCert_Key
END