SQL Server CLE Encryption Implementation
Based on your requirements, I would actually recommend Transparent Data Encryption (TDE) instead of CLE.
TDE encrypts at the database level and therefore you don't have to worry about being compromised by storing the decryption process in a stored procedure. Rather, TDE works on the traditional private and asymmetric key pattern to prevent access to the unencrypted data without the proper key (which you can handle authenticating outside of the database to decouple the security concerns).
It's also better in my opinion because it doesn't force you to change your data types to VARBINARY which almost certainly could affect the performance of your queries. Rather it keeps everything as you designed under the hood (hence Transparent) when you access your database.