I have a SP that accepts a user-defined table type
table and does inserts and updates to a database table.
Example:
CREATE PROC [dbo].[MySP1] (@InsertData dbo.MyDataType READONLY)
AS
BEGIN
INSERT INTO dbo.MyTable (A, B, C)
SELECT ia.A, ia.B, ia.C
FROM @InsertData AS ia
END
Recently, I have decided to encrypt one column of that database table (dbo.MyTable
) using Always Encrypted.
Now, in order to keep the SP operational, I (guess I) will have to update the user table type (dbo.MyDataType
) to contain an Always Encrypted
column.
But this doesn't seem possible to me, neither from the SSMS GUI, nor via T-SQL.
E.g., this fails:
CREATE TYPE [dbo].[MyDataType] AS TABLE(
[A] [INT] NULL,
[B] [VARCHAR](MAX) NULL,
[C] [VARCHAR](MAX) COLLATE Danish_Norwegian_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = RANDOMIZED, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL
)
Is this possible? If not, are they any alternatives? Passing the the table values as scalar values to the SP is not very possible, since I will have to call that SP as many times as the number of the rows in the original user defined table.