Passing Multiple T-SQL Queries To sp_execute_external_script Stored Procedure And Loop Back Requests

If you want to do Advanced Analytics or Predictive Modeling directly into SQL Server, you can since the version 2016 thanks to SQL Server Machine Learning Services. In particular, you can take advantage from the extensibility framework running scripts in R, Python or Java (only available from the version 2019) directly into a system stored procedure, the sp_execute_external_script one.

Looking at the sp_execute_external_script syntax, among others, it accepts also the @input_data_1 parameter. It allows to pass the input data used by the external script in the form of a T-SQL query. The first question you’ll ask yourself is: “Ok, the parameter name is in the form of input_data_N. So I suppose multiple input data can be passed to the stored procedure!”. And guess what? The answer is: “No, you can’t!”. Maybe Microsoft called this parameter in such a way so that in the future you’ll be able to add multiple input data sets using the input_data_2 or input_data_3 and so on. But now you can’t do that.

When you’re working in SQL Server, it’s better to take advantage of the database engine whenever it is possible for any data transformation. However, there are cases in which you need to use more than one data flow into the external script, due, for example, to very special transformation only available in the external language. Thankfully it’s possible to connect back to SQL Server from the external script using an ODBC…