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

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

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