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?