The sp_execute_external_script  system stored procedure is the stored procedure which invokes the external environment to run an external script (either Python or R ) in a T-SQL script.

This is quite highlights of the sp_execute_external_script stored and some short notes on it.

let’s quickly look at the structure of the stored procedure

sp_execute_external_script
@language = N’language,
@script = N’script’
[ , @input_data_1 = N’input_data_1′ ]
[ , @input_data_1_name = N’input_data_1_name’ ]
[ , @output_data_1_name = N’output_data_1_name’ ]
[ , @parallel = 0 | 1 ]
[ , @params = N’@parameter_name data_type [ OUT | OUTPUT ] [ ,…n ]’ ]
[ , @parameter1 = ‘value1’ [ OUT | OUTPUT ] [ ,…n ] ]

Full explanation of the sp_execute_external_script  stored procedure and its arguments can be found here:

sp_execute_external_script explained

For reasons why the @script and other parameters to the sp_execute_external_script  stored procedure are stored as nvarchar hence prefixed with “N” check out this excellent explanation and illustration by Aaron Bertrand on stackexchange.com here

The results returned by the external script (Python or R) from the executed stored procedure does not have column names with the returned results. The column names were actually local to the external script so your results will output with only raw values but obviously in a tabular form.

To define new columns names and specify metada for these results you can use the WITH RESULT SET clause with the sp_excecute_external_script stored procedure.

For detailed explanations of this  WITH RESULT SETS clause, check this thread

To install packages which are not in the in-database R of the SQL Server, check out these guides

Hope this gives you quick links on the overview of the sp_execute_external_script stored procedure

People Who Read The Above Post Also Read This:

SQL Statement Query Clause Evaluation Order – Which Part of SQL Statements Execute First and in What Order? Splitting Values in one Column to Multiple Columns using CASE Expression Statement SQL Server Get Numbers and Percentages from Tables – Microsoft Sql Server Great Quick Transact-SQL Server Tutorials – Quick Revision and General Understanding