SQL Server sp_execute_external_script Stored Procedure – Get Started with SQL Server Machine Learning Services – Part 3

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,

Fig. 1 Simple Output in SQL Server

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)
'
Fig. 2 Arithmetic Operation using Python Code in SQL Server

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)
'
Fig. 3 Appended Strings in Python

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)
'
Fig. 4 Arithmetic Operations in Python using Variables in SQL Server

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.

Other Articles in this Series

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Website Built with WordPress.com.

Up ↑

%d bloggers like this: