You raise some valid questions there. Indeed, if it is possible to retrieve the encrypted data by running a stored procedure, you only have security by obscurity.

I can see three ways to go. One is to use passphrases to protect the keys or the data (with encrypt/decryptbypassphrase) and send in the passphrase from the application. At least you need two components to get to the data.

A second option is provide the encryption key with a hardware device plugged into the server. This solution requires physical access to the server to remove/attach the key device. This works well for a laptop database, but alas EKM is only available in Enterprise Edition which you don't run on a laptop.

The last option is to encrypt client side. This is likely to be the most secure, but also means that you cannot perform any server-side analysis of the data.