Execute Dynamic SQL Generated From Select

Question

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.

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 

Results

Does one concatenate the results and call Exec?

Answer 1

While I would prefer to generate true dynamic SQL statements with strongly-typed parameters, concatenation is probably ok here because the proper way will be a lot more cumbersome. I do, however, prefer sp_executesql over EXEC(), always.

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql = @sql + N'EXEC [sim].[ROI_Save] ' 
        + CONVERT(varchar(6), VSL.PathId)           + N', '
        + CONVERT(varchar(6), @ROIResultId)         + N', '
        + CONVERT(varchar(6), RT.routeTypeResultId) + N', '
        + CONVERT(varchar(6), RT.ROI_RouteTypeId)   + N'; '
FROM  @routeResultIds AS RT
JOIN sim.ROI_Result_Vehicle AS VSL 
ON RT.ROI_Result_VehicleId = VSL.ROI_Result_VehicleId;

EXEC sys.sp_executesql @sql;

I still don't like that the parameters to the procedure aren't named, and if any of your parameter values were strings or dates, the build-up of single quotes would quickly get very annoying.

Answer 2

Select into a table of commands. Then execute the commands in a while loop.

OR

Build your input data into a table and loop through it using sp_executesql with parameters.