I have a customer that wants to eliminate direct queries to enterprise databases and invoke stored procedures. This will ease maintenance issues down the line if underlying schema is changed. At any rate the Call Stored Procedure returns the number of rows affected by the call, not the result set(s). I'm hoping I'm missing something in the docs. There has to be a way to execute a stored procedure that returns the result sets.
You're not missing anything. The current implementation of the stored procedure can only return the number of rows affected. They are looking at fixing that in the next release.
The alternative is to use the Execute Script service and use jdbc code to execute your stored procedure. See http://forums.adobe.com/message/2302938#2302938 for an example.