How to select value to stored procedure parameter from always encrypted column

Question

I have Encrypted one column mob_no in a table. after encrypted the column, stored procedure is getting error.

I have added the sp below

create procedure get_cut

@mobNo varchar(50),

@custId int 

As 

Begin

if(@mobNo = null or @mobNo = '')

Begin

    select @mobNo = mob_no  
    from table1 where cust_id = @custId



End

select cust_name from tbl_cust where mob_no = @mobNo and cust_id = @custId


End

When run this sp I got the bellow error

Msg 33299, Encryption scheme mismatch for columns/variables '@mobNo'. The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '9' expects it to be (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'xxxx', column_encryption_key_database_name = 'mydb') (or weaker).

score 1 · Accepted Answer · answered Dec 08 '16 at 23:59

The following equality condition

 @mobNo = ''

in your stored procedure will be evaluated in the server. Since you are using Always Encrypted, the column corresponding to mobNo is stored in the server as binary data. the server will not be able to compare the values in the database against '' because the server does not know the encrypted binary value corresponding to ''

You can modify the stored procedure to take 3 arguments instead of 2 as follows:

create procedure get_cut
@mobNo varchar(50),    
@custId int,
@emptyString varchar(50)
...
if(@mobNo is null or @mobNo = @emptyString)
...

Note: is null instead of = null

you can pass the value '' as

a parameter to your SqlCommand, if you are using an application to execute your stored procedure or
if you are using Sql Server Management Studio to execute your stored procedure, lookup Parameterization for Always Encrypted, to understand how to send plaintext values targeting encrypted columns

As an example, From SSMS (Requires at least SSMS version 17.0.) you can do the following:

Declare @emptyStringVar varchar(50) = ''
Declare @mobNoVar varchar(50) = '000-000-0000'
EXEC get_cut 
@mobNo varchar = @mobNoVar  
@custId int = 1234
@emptyString @emptyStringVar

How to select value to stored procedure parameter from always encrypted column

1 Answers1