SP alternate with column encryption

Question

We have been using the stored proc below to search a table named tblPatients. However, now that we have encrypted the name fields the sp fails. Is there a workaround like temp table or something?

ALTER PROCEDURE [dbo].[kd_selPatientSearch]
    @SearchText     nvarchar(150),
    @SearchType     int = 0

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT P.FirstName, 
            P.LastName, 
            P.CaregiverLogin, 
            O.Organization, 
            O.OrgUserName
      FROM dbo.tblPatients AS P INNER JOIN
           dbo.tblOrganization AS O ON P.OrgID = O.OrgID
     WHERE (CASE WHEN @SearchType = 0 AND P.CaregiverLogin = @SearchText THEN 'T'
                 WHEN @SearchType = 1 AND (P.LastName = @SearchText OR P.FirstName = @SearchText) THEN 'T'
                 ELSE 'F'
                 END = 'T')
    ORDER BY LastName, FirstName;

Answer 1

Since I have seen your table in a different thread, I can tell you that this procedure is a dead case in its current shape. The columns LastName and FirstName are encrypted, whereas CaregiverLogin is not. That information should by now be enough to tell you why this can't work, so I say no more.

So you will have to split @SearchText in two parameters - at least. One for CaregiverLogin, and one for the other two. However, FirstName is nvarchar(20) and LastName is nvarchar(30), so I am not sure that you can use the same variable with both - Always Encrypted is, as you have noted, quite picky about data types.

Answer 2

I really pared it down to below but when I run it I get error "Encryption scheme mismatch for columns/variables . The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '3' expects it to be (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'kdctest') (or weaker). "

ALTER PROCEDURE [dbo].[kd_selPatientSearch]
    @FirstName      nvarchar(20)

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT P.FirstName, 
            P.LastName, 
            O.Organization, 
            O.OrgUserName
      FROM dbo.tblPatients AS P INNER JOIN
           dbo.tblOrganization AS O ON P.OrgID = O.OrgID
     WHERE P.FirstName = @FirstName;

END