The concern isn't so much about where you perform encryption/decryption operations, but how you are managing keys, encrypted data, and how you actually go about the encryption itself.
The immediate concern I have with doing this through the use of stored procedures is that the key will most easily be accessed from within the database tier. This presents a vulnerability in that your database now presents as a component that if it alone becomes compromised, then your sensitive data is almost assuredly compromised as well.
The above is a REAL vulnerability that will prevent you from achieving standards compliance across a number of different standards (Eg. HIPAA, PCI, etc..)!
Ultimately the weakest point in a symmetric encryption scheme is weakness in how keys are managed. Some of the most secure (and expensive) solutions utilize something called an HSM that will securely store keys and provide them as needed to applications in a secure way.
Other ways you can solve this problem is by encrypting the channel between your application and database servers, and requiring the application servers to provide the key to stored procedures for encryption/decryption. This is a good solution because an attacker that compromised the database would not have access to the key, or if the attacker compromised the application server then they would not have access to the database (theoretically). This prevents a single point of failure.