Stored procedure cannot be the input to the Python script in MS SQL Server

I am in the journey of using python in MS SQL Stored procedures.

I am big fan of stored procedures since my earlier days in Sybase in 1993 up till now. 

The same for Oracle packages and procedures. 

With stored procedures , we can encapsulate all the business and data logic.

Being the stored procedure is inside the database , the data and logic is secure , highly performant and so on. 

In essence , it is an API that can be called from any languages / tools. 

MS SQL server started supporting embedding python scripts in T-SQL. 

Unfortunately , the input for the python cannot be stored procedure. 

Hopefully , it is changed in the coming releases. 

Here we go 

a) We will create a table emp with 2 records in it. 

C:\>sqlcmd -W -S DBSER\SQL2019DEV -d rptsdb

1>

2> select * from emp;

3> go

id fname lname

-- ----- -----

1 Jane Doe

1 John Doe

(2 rows affected)

b) Create python external script with simple select as the input. 

1> EXECUTE sp_execute_external_script @language = N'Python',

2> @script = N'

3~ ## print(InputDataSet)

4~ OutputDataSet = InputDataSet

5~ ',

6> @input_data_1 = N'SELECT fname  , lname  FROM emp  ORDER BY 1;'

7> WITH RESULT SETS ((  fname nvarchar(50), lname nvarchar(50)     ));

8> go

fname lname

----- -----

Jane Doe

John Doe

(2 rows affected)

c) Create stored procedure to list the records from the table 'emp' 

1> create procedure p_emp_list

2> as

3> begin

4> SELECT fname , lname

5> FROM  emp

6> end;

7> go

1> exec p_emp_list;

2> go

fname lname

----- -----

Jane Doe

John Doe

(2 rows affected)

d) Let us use the above stored procedure as the input to Python script 

1> EXECUTE sp_execute_external_script @language = N'Python',

2> @script = N'

3~ ## print(InputDataSet)

4~ OutputDataSet = InputDataSet

5~ ',

6> @input_data_1 = N'exec p_emp_list;'

7> WITH RESULT SETS ((  fname nvarchar(50), lname nvarchar(50) ));

8> go

Msg 39001, Level 16, State 2, Server DBSER\SQL2019DEV, Line 1

Only SELECT statement is supported for input data query to 'sp_execute_external_script' stored procedure.

Msg 11536, Level 16, State 1, Server DBSER\SQL2019DEV, Line 1

EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), but the statement only sent 0 result set(s) at run time.