Using SQL Server Always Encrypted feature in stored procedure

I am using SQL Server Always Encrypted feature of Azure SQL with .NET code. While it works like charm inside Entity Framework where the data can be decrypted, not sure how do I use it in stored procedures?

Let’s say I have a column called Department Id and I have encrypted the same using Always Encrypted feature. If I want to join using Department Id to other tables and I want to decrypt it on the fly in the SQL Server stored procedure, how do I do that?

Answer

If I want to join using Department Id to other tables

Use Deterministic encryption:

Using deterministic encryption allows point lookups, equality joins, grouping and indexing on encrypted columns.

---

and I want to decrypt it on the fly in the SQL Server stored procedure, how do I do that?

You don’t. The server never has access to the key. That’s the point. You can return the value from the stored procedure but you don’t decrypt it on the SQL side.