Passing T-SQL select statement to sp_execute_external_script

Passing T-SQL Statement to sp_execute_external_script has couple of limitations which are not mentioned on MSDN or on known issues list. Or I haven’t seen it yet on MSDN.

So in this blog post I will discuss what are the limitations of passing T-SQL Statement to sp_execute_external_script as a input data-set.

As the name of the blog post suggests, only T-SQL SELECT statement are allowed as input data-set. Any other form of statement being pushed as T-SQL into input data will yield this error:

2016-06-19 23_28_26-SQLQuery7.sql - SICN-00031_SQLSERVER2016RC3.Sandbox (SPAR_si01017988 (55))_ - Mi

So I generated this error with passing following T-SQL:

EXECUTE sp_execute_external_script    
        @language = N'R'    
      ,@script=N'OutputDataSet<- InputDataset'
      ,@input_data_1 = N'EXECUTE sample_data'

So passing stored procedure into @input_data_1 parameter is not allowed!

Trying to start with WITH CTE statement:

EXECUTE sp_execute_external_script    
        @language = N'R'    
      ,@script=N'OutputDataSet<-InputDataSet;'
      ,@input_data_1 = N'WITH CTE
                                AS ( SELECT name,number,[type]
                                FROM master..spt_values
                                WHERE [type] = ''EOD'')
                                SELECT * FROM CTE'
WITH RESULT SETS (
                     (name VARCHAR(50)
                    ,number INT
                    ,[type] CHAR(3))
                    );

The script returns the values resolved from the query.

It also handles UNION operation in select statement:

EXECUTE sp_execute_external_script    
       @language = N'R'    
      ,@script=N'OutputDataSet<-InputDataSet'
      ,@input_data_1 = N'SELECT 1 as cifra UNION SELECT 5 as cifra'
WITH RESULT SETS (( cifra VARCHAR(10)));

So you correctly predicted, if it supports UNION, it also supports UNION ALL, EXCEPT and INTERSECT.

Batch terminators or semicolons are also not allowed:

EXECUTE sp_execute_external_script    
        @language = N'R'    
      ,@script=N'OutputDataSet<-InputDataSet;'
      ,@input_data_1 = N' SELECT 1; 
                            GO 10'
WITH RESULT SETS UNDEFINED;

 

Working with variables. Declaring variable within input_data parameter will return error:

EXECUTE sp_execute_external_script    
        @language = N'R'    
      ,@script=N'OutputDataSet<-InputDataSet;'
      ,@input_data_1 = N'DECLARE @i INT = 1
                         SELECT @i;' 
WITH RESULT SETS UNDEFINED;

 

Whereas, both parameters @script and @input_data_1 can take value from an external variable when passed through procedure, as in example below:

CREATE PROCEDURE pp_tt 
    (@i INT)
AS

DECLARE @SQL NVARCHAR(MAX)
SET @SQL = 'SELECT '+CAST(@i AS CHAR(10))+';'

EXECUTE sp_execute_external_script    
        @language = N'R'    
      ,@script=N'OutputDataSet<-InputDataSet;'
      ,@input_data_1 = @sql
WITH RESULT SETS UNDEFINED

EXECUTE pp_tt @i=23

 

Please note: paramaters in sp_execute_external_script can not be furhter parametrized. Only assigned with variables, which is further parametrized and have a input varible defined (as shown in above example). To support this, following this example will return an error:

CREATE PROCEDURE pp_tt 
    (@i INT)
AS
EXECUTE sp_execute_external_script    
        @language = N'R'    
      ,@script=N'OutputDataSet<-InputDataSet;'
      ,@input_data_1 = 'SELECT '+CAST(@i AS CHAR(10))+';'
WITH RESULT SETS UNDEFINED;

 

What about logical conditions (IF Statement) or LOOP statements (WHILE, FOR). To put it briefly: no! 🙂 Same error.

 EXECUTE sp_execute_external_script    
        @language = N'R'    
      ,@script=N'OutputDataSet<-InputDataSet;'
      ,@input_data_1 = N'IF RAND() <= 0.5
                            SELECT 1 AS ResultIF
                            ELSE SELECT 2 AS ResultIF;' 
WITH RESULT SETS UNDEFINED;

With logical statements, you can not use IF Statement even if you declare special variable and pass it into @input_data_1 parameter within stored procedure:

CREATE PROCEDURE IFY_IF
AS

DECLARE @IF NVARCHAR(MAX)
SET @IF = 'IF RAND() <= 0.5
            SELECT 1 AS ResultIF
            ELSE SELECT 2 AS ResultIF;'

EXECUTE sp_execute_external_script    
        @language = N'R'    
      ,@script=N'OutputDataSet<-InputDataSet;'
      ,@input_data_1 = @IF
WITH RESULT SETS UNDEFINED;

EXECUTE IFY_IF

Same logic applies to FOR, WHILE statements.

This tests could be long and many many other things could be taken into consideration.To wrap up, following T-SQL Statements can not be used within @input_data_1 parameter:

  • stored procedure
  • logical IF and WHILE, FOR loops
  • temporary variables or tables
  • no updates, inserts or deletes (only select)
  • GO statement or semicolon
  • OUTPUT clause from and DML statetement
  • CURSORS

Following statements (besides SELECT statement) can be used:

  • SELECT with multiple JOINS
  • WITH Common table expressions
  • UNION, UNION ALL, EXCEPT, INTERSECT
  • any SET, STRING, LOGICAL, COMPARISON, BITWISE, ARITHMETIC, COMPOUND operators
  • COLLATE

 

This is so far what I have tested. Feel free to add any test at Github or comment section below. I will test more possibilities for this parameter and will update my blog.

All of the code (and more) from this blog post used, is available at Github.

Happy R-SQLing!

Advertisements

3 thoughts on “Passing T-SQL select statement to sp_execute_external_script

  1. Since stored procedure calls, table variables and temp tables are not supported, do you know is there any way to encapsulate the SQL code other than what you can accomplish with a view? Seems like a testing headache not to be able to develop and test the SQL as SQL, independently of the sp_execute_external_script call.

    Like

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s