I understand why SQL won't allow this, I want to know if there is some way around this that will allow me to do a where on an encrypted column with multiple values?
I have tried to use a TVP, but SQL does not allow me to join on an encrypted column:
ALTER PROCEDURE GetUsers
@Users [dbo].[Users_Type] readonly
AS
BEGIN
SELECT *
FROM [dbo].[Users] u1
JOIN @Users u2
ON u1.[Username] = u2.[Users]
END
GO
I've tried creating the TVP with an encrypted column, but SQL won't allow me to do so:
CREATE TYPE [dbo].[Users_Type] AS TABLE(
[Users] [varchar](50) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL
)
GO
Msg 102, Level 15, State 1, Line 8 Incorrect syntax near 'ENCRYPTED'.
Is there any way to get around this limitation?