A business process in SQL can generate a variable to each operation 1-N number of calls to a stored procedure whose purpose is to insert data related to the main process.
How does one execute multiple rows of generated sql from a select or is there a way of calling a stored procedure from a select?
The process before this step is an involved operation of business logic and data driven operations and has done subsequent inserts to related FK joined tables.
At that point where it needs to call a stored procedure it has data ready to be processed in a table variable. The step (logic inside the proc) calling the stored proc is an involved process that makes sense to be in a stored proc and cannot be copied to the existing proc.
Faux example of generated code generated by
SELECT 'EXEC [sim].[ROI_Save] ' + CONVERT(varchar(6), VSL.PathId) + ', ' + Convert(varchar(6), @ROIResultId) + ', ' + CONVERT(varchar(6), RT.routeTypeResultId) + ', ' + CONVERT(varchar(6), RT.ROI_RouteTypeId) + '; ' FROM @routeResultIds RT JOIN sim.ROI_Result_Vehicle VSL ON RT.ROI_Result_VehicleId = VSL.ROI_Result_VehicleId
Does one concatenate the results and call