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
Posted in Uncategorized
3 comments on “Passing T-SQL select statement to sp_execute_external_script
  1. Very nice article. You can further parametrize your both your sql statement (using multiple parameters) or the R script. Please see: http://stackoverflow.com/questions/37677281/filter-a-ssrs-report-based-on-a-r-stored-procedure

    Liked by 1 person

  2. tomaztsql says:

    Thanks for sharing this link to multiple parameters.
    This is what I wanted to cover in my next blog post; using parameter @params and multiple input parameters.

    Like

  3. 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 )

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 )

w

Connecting to %s

Categories
Follow TomazTsql on WordPress.com
Revolutions

Tomaz doing BI and DEV with SQL Server and R

tenbulls.co.uk

attaining enlightenment with sql server, .net, biztalk, windows and linux

SQL DBA with A Beard

He's a SQL DBA and he has a beard

DB NewsFeed

Matan Yungman's SQL Server blog

Reeves Smith's SQL & BI Blog

A blog about SQL Server and the Microsoft Business Intelligence stack with some random Non-Microsoft tools thrown in for good measure.

SQL Server

for Application Developers

Clocksmith Games

We make games we love to play

Business Analytics 3.0

Data Driven Business Models

SQL Database Engine Blog

Tomaz doing BI and DEV with SQL Server and R

Search Msdn

Tomaz doing BI and DEV with SQL Server and R

R-bloggers

Tomaz doing BI and DEV with SQL Server and R

Ms SQL Girl

Julie Koesmarno's Journey In Data, BI and SQL World

R-bloggers

R news and tutorials contributed by (750) R bloggers

Data Until I Die!

Data for Life :)

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009

Grant Fritchey

Intimidating Databases and Code

Madhivanan's SQL blog

A modern business theme

Alessandro Alpi's Blog

SQL Server, Azure and .net in a nutshell :D

Paul te Braak

Business Intelligence Blog

Sql Server Insane Asylum (A Blog by Pat Wright)

Information about SQL Server from the Asylum.

Gareth's Blog

A blog about Life, SQL & Everything ...

SQLPam's Blog

Life changes fast and this is where I occasionally take time to ponder what I have learned and experienced. A lot of focus will be on SQL and the SQL community – but life varies.

William Durkin

William Durkin a blog on SQL Server, Replication, Performance Tuning and whatever else.

$hell Your Experience !!!

As aventuras de um DBA usando o Poder do $hell

%d bloggers like this: