Amir,
Hope, the following explanation would clarify some of your questions.
First, you are right when you said that you specify in deployment descripter whether the bean is a CMP or BMP.
Deploying, and the usasge of CMP is verymuch tied to the vendor of the Container. I am not aware of any Application servers that support Stored Procedures for CMP beans.