What are the considerations for loading Data Pages that perform stored procedure calls?
These would have to be performed as Connect-SQL calls from an Activity, and the only channel to accomplish this in a Data Page is to use such an Activity as its Data Source. The considerations are no different when the Activity is used to load a Data Page. Ideally in contemporary system integration, the Stored Procedure would have an API -- like a REST API -- in front of it so that the Pega application is not concerned with having to make a Stored Procedure call that couples the Pega application to the vendor of the database.
If invoking a Stored Procedure from Pega is unavoidable, "hide it" in the Data Source of a Data Page so that the rest of the application does not have to be aware that this is the implementation approach. As with all Data Pages, define a clear "contract" of inputs (parameters), outputs (Data Page content) and expected behavior (e.g. exception handling) that the Data Page fulfils for the application, regardless of its actual Data Source.
Should the Stored Procedure eventually be superseded by a REST API or other integration approach, the Data Page becomes the only component of the Pega application that is refactored. The remainder of the application is unaffected by the change in implementation so long as the agreed "contract" remains fulfilled.