Introduction
When we want to execute any external language script in SQL Server, then we have to sp_execute_external_script stored procedure. sp_execute_external_script is a new system stored procedure which was introduced in the SQL Server 2016. It is mainly used to execute the Machine Learning Services such as Python and R in SQL Server.
Executing Python in SQL Server
In this article series, we are primarily focused on Python, so now lets see how we can execute a Python code in SQL Server. As stated before, we have to execute the Python code using sp_execute_external_script and this stored procedure has two required arguments.
- @language – In our scenario its value is always Python
- @script – Include our Python code that we want to execute
Simple Example
EXECUTE sp_execute_external_script
@language=N'Python',
@script=N'
print("Learn With Sundar - ML Services Demo")
'
Remember the characters are case sensitive. After a while we will get result as,
We can also perform any operations on this. Lets try a simple arithmetic operation.
EXECUTE sp_execute_external_script
@language=N'Python',
@script=N'
print(123*1234590)
'
Declaring Variables in Python
We no need to declare the variable as we do it in SQL Server. All we need to do is just assign the value to variable. Lets have a look, I am going to create two variables as i and j and allocate a string value and append these two variables.
EXECUTE sp_execute_external_script
@language=N'Python',
@script=N'
i="Hello "
j="Learn With Sundar"
print(i+j)
'
Lets see another example to perform some arithmetic operation using variables,
EXECUTE sp_execute_external_script
@language=N'Python',
@script=N'
i=109358
j=905
print(i*j)
'
Other Parameters
As for now we have seen the required parameters of sp_execute_external_script stored procedure, now lets see other option parameters of this stored procedure.
Input Parameter
- @input_data_1 is a nvarchar(max) variable and it is used to store the SELECT command.
- @input_data_1_name is a nvarchar(128) variable and it is used to give a name for the above SELECT command. This name is how it will be recognized by the external script stored in the @script parameter.
Output Parameter
The sp_execute_external_script stored procedure returns data in a data frame. The default data frame name is OutputDataSet. If you want to change the data frame name you need to provide the desired name with the @output_data_1_name parameter (a nvarchar(128) variable).
Conclusion
In this article we discussed about the System Stored Procedure sp_execute_external_script and how we can execute our Python code in SQL Server. I hope you all found this article much useful. Please share your feedback in the comment section. Lets discuss more concepts in Machine Learning Services in SQL Server in our upcoming articles.
Learn With Sundar 