I faced the below problem when I tried to execute a store proc
Msg 33277, Level 16, State 6, Line 27
Encryption scheme mismatch for columns/variables '@SSN1'. The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '0' expects it to be DETERMINISTIC, or PLAINTEXT.
I followed the below link to create a store proc
https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-refresh-param...
1. I created the below table
CREATE TABLE [Patients] (
[PatientID] INT IDENTITY(1, 1) NOT NULL,
[SSN] CHAR(11),
[Name] NVARCHAR(50) NULL,
CONSTRAINT [PK_Patients] PRIMARY KEY CLUSTERED ([PatientID] ASC)
);
insert into Patients(ssn, name) values(1,'test1')
insert into Patients(ssn, name) values(2,'test2')
insert into Patients(ssn, name) values(3,'test3')
2. I created the below SP
CREATE PROCEDURE [find_patient1] @SSN CHAR(11)
AS
BEGIN
SELECT Name FROM [Patients]
WHERE SSN = @SSN
END;
CREATE PROCEDURE [find_patient2] @SSN CHAR(11)
AS
BEGIN
SELECT Name FROM [Patients]
WHERE SSN = @SSN
END;
CREATE PROCEDURE [find_patient3] @SSN CHAR(11)
AS
BEGIN
SELECT Name FROM [Patients]
WHERE SSN = @SSN
END;
3. When I executed
declare @SSN1 as CHAR(11) = '1'
EXEC find_patient1 @SSN1
declare @SSN2 as CHAR(11)
set @SSN2= '1'
EXEC find_patient1 @SSN2
I could see the output : test1
4. I encrypted the SSN Column using always encrypted
5. Executed
EXEC sp_refresh_parameter_encryption [find_patient1];
EXEC sp_refresh_parameter_encryption [find_patient3];
EXEC sp_refresh_parameter_encryption [find_patient2];
6. When I tried to run
declare @SSN as CHAR(11)
set @SSN='2'
EXEC find_patient1 @SSN
declare @SSN1 as CHAR(11) = '1'
EXEC find_patient1 @SSN1
I am getting the below error :
Encryption scheme mismatch for columns/variables '@SSN1'. The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '0' expects it to be DETERMINISTIC, or PLAINTEXT.
Am I missing something here?
I follow the same procedure as in
https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-refresh-param...
Also, I restarted the Sql-Server service still not working.
The same issue even if I have only one stored proc
Am I missing anything ?
