2

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

enter image description here

Does one concatenate the results and call Exec?

5

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.

  • My original sql code named the parameters and in the final version I will place them into the dynamic SQL. As to the current variables to the proc having either datetimes or strings; fortunately it is all int variables. (If it wasn't I could re-architect the proc to make it easier to call dynamically). thx – ΩmegaMan Oct 22 '15 at 13:57
  • @OmegaMan - Sure would hate for RT.ROI_RouteTypeId to contain '';DROP DATABASE [xyz]; Please ensure you read and understand about the possibilities of sql injection – Max Vernon Oct 22 '15 at 14:09
  • 1
    @MaxVernon Your point is well taken and fortunately this is not a public facing process. All the variables are based off of Ints. and all data preceding this point is generated for this process. :-) – ΩmegaMan Oct 22 '15 at 15:03
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.