The stored procedure 'encryption' process is just obfuscation. It uses a standard algorithm and is relatively easy to reverse. If you Google 'sql server stored procedure decrypt' the top two entries at the time of this post give you methods to decrypt your procedures.

You need to think about what you are trying to guard against. If you are trying to safeguard your server against a determined attack, then encrypting the stored procedures should be near the bottom of the list - if a determined attacker has has got enough access to look at your stored procedures then almost certainly they will have the tools to undo the obfuscation.

If you just want to let someone with DBA access know the procedure contains something sensitive that should not be looked at unless really necessary, then 'encryption' is a reasonable way to solve this. Maybe not the best way, but a reasonable way.

An important thing to remember is that if 1% of your procedures are encrypted, then most people will not bother with getting a decrypt tool. If 100% of procedures are encrypted then all DBAs will need a decryption tool as part of their troubleshooting toolkit.