CLR Assembly for Encryption/Decryption

Question

We recently implemented some symmetric keys in MS SQL 2005 for encrypting and decrypting credit card, check routing/account numbers.

Ideally, we would like a user defined function to be able to perform the encryption and decryption, however, its not possible since the encryptbykey and decryptbykey functions cannot be used within user defined functions.

Initially I thought we could create a CLR Assembly (in C#) with functions to do this, however, as I learned the same rules apply.

I was wondering what others have been doing to easily perform this functionality. Creating many stored procedures such as:

OPEN SYMMETRIC KEY...
SELECT username, decryptByKey(...) AS password CLOSE SYMMETRIC KEY...

... for the many areas in which we will encrypt/decrypt would be very tedious.

Answer 1

Whether it's a good idea or not, what about a stored procedure with output parameter to achieve the same as a udf?