Dynamic SQL offers flexibility, as queries can be constructed and executed at runtime, making it ideal for scenarios with unpredictable or highly variable query needs. Conversely, Embedded SQL emphasizes stability and efficiency by integrating SQL code directly into application logic, offering optimized response times for predefined query patterns.

Just a quick note about Embedded vs Dynamic: in recent versions of IRIS, both types are compiled at runtime and both make use of Universal Query Cache, the differences in efficiency and performance should be minor, if any.

I think the benefit of SQLProcs is that your stored procedure is executed as your data is retrieved. This means that data retrieval and processing happen in one fell swoop rather than being broken into two stages where you loop over your table to retrieve data then loop again over the results to process them. This, I hope, can save you a lot of compute time. Of course, this is all conjecture, the best way to know is to benchmark both approaches.

Wrapping your application logic in a query is a great idea. You are basically delegating the task of retrieving your data in the most optimal way to the SQL Engine, so that the engine has to figure out whether to use indexes, parallelize data retrieval, create temp file to store intermediary results, clean up etc rather than you having to do it.