'Can I query a parameter and then use it in a where clause when the field it compares to is set for Randomized Encryption in a secure enclave?
I have a table where some columns are encrypted using Randomized Encryption. I'm using a secure enclave and can save and query records fine. I am writing a stored procedure that takes a few parameters and gets records matching the values. Here's a simplified, contrived example where the FirstName and LastName fields are encrypted.
CREATE OR ALTER PROC SearchForPerson
@FirstName NVARCHAR(512),
@LastName NVARCHAR(512)
AS
BEGIN
IF @LastName != ''
-- select * from Person where LastName = @LastName;
select * from Person where FirstName = @FirstName;
END
Note the select statement that's commented out. This will save the SP with no errors. If I uncomment that statement, I'll get an error (the line numbers are not real; this is just a sample):
Msg 33277, Level 16, State 6, Procedure SearchForPerson, Line 6
Encryption scheme mismatch for columns/variables '@LastName'. The
encryption scheme for the columns/variables is (encryption_type =
'PLAINTEXT') and the expression near line '8' expects it to be
Randomized, , a BIN2 collation for string data types, and an enclave-
enabled column encryption key, or PLAINTEXT.
Msg 8180, Level 16, State 1, Procedure sp_describe_parameter_encryption, Line 1
Statement(s) could not be prepared.
Is there a way to test for a valid value of the parameter and then also use it to filter the data? Note this is a very contrived example; in reality, the If statement would be a bit more complex.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
