<p><div><div><div><div data-width="100" data-show-faces="false" data-send="true" data-layout="button_count" data-height="21" data-href="https://www.r-bloggers.com/using-parameter-and-multiparameters-with-sp_execute_external_script/"></div></div><div><a data-url="https://www.r-bloggers.com/using-parameter-and-multiparameters-with-sp_execute_external_script/" data-counturl="https://www.r-bloggers.com/using-parameter-and-multiparameters-with-sp_execute_external_script/" data-count="horizontal" data-text="Using parameter and multiparameters with  sp_execute_external_script" class="twitter-share-button" data-via="rbloggers" href="https://twitter.com/share"></a></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://tomaztsql.wordpress.com/2016/08/01/using-parameter-and-multiparameters-with-sp_execute_external_script/"> R – TomazTsql</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>With RTM version of SQL Server 2016, sp_execute_external_script stored procedure has undergone couple of changes prior to it’s final outlook. The parametrization of this external procedure somehow resembles a typical extended stored procedure.</p>
<p>Indeed, sp_execute_external_script is an extended stored procedure written using CLR (whereas stored procedures are natively written in T-SQL) and their main purpose it that they run external commands that a normal T-SQL stored procedure could not handle.</p>
<p>Those who are (have been) working with any kind of external stored procedure or stored procedure using</p>
<pre>AS { EXTERNAL NAME assembly_name.class_name.method_name }</pre>
<p>you will be familiar with the sp_execute_external_script notation.</p>
<pre>  EXECUTE sys.sp_execute_external_script
          @language = 
         ,@script = 
         ,@input_data_1 = 
         ,@input_data_1_name =
         ,@output_data_1_name =
         ,@parallel =
         ,@params = 
         ,@parameter1 =</pre>
<p> </p>
<p>Parameters @params and @parameter1 are interesting, but what might be a bit puzzling are numbers at the end of the names of @input_data<strong>_</strong>1, @input_data_1_name,… They have  no technical meaning (as far as I have found out) since they don’t enumerate anything and if you by common sense create @input_data_2 parameter, you will get an error in return. In a way this error would have been expected, since joining two SQL Statements into one R dataset would just be nonsense. It is more likely that numbers just denote data columns or data parameters can be enumerated within string value of a particular input parameter and that you need at least one of the items if you are using this parameter.</p>
<p>So parameters with enumerator number in the names, these parameters can hold more values and both parameters @params and @parameter1 are paired:</p>
<p>@params is  list of input parameter declarations  and</p>
<p>@parameter1 is list of values for the input parameters</p>
<p>just like for @input_data_1 and @input_data_1_name parameters.</p>
<p>Simple example would be getting Chi-Square value and statistical significance in one run out of R:</p>
<pre>USE WideWorldImporters;
GO

DECLARE @F_Value VARCHAR(1000)
DECLARE @Signif VARCHAR(1000)


  EXECUTE sys.sp_execute_external_script
          @language = N'R'
         ,@script = N'mytable &lt;- table(WWI_OrdersPerCustomer$CustomerID, WWI_OrdersPerCustomer$Nof_Orders) 
                     data.frame(margin.table(mytable, 2))
                     Ch &lt;- unlist(chisq.test(mytable))
                     F_Val &lt;- as.character(Ch[1])
                     Sig &lt;- as.character(Ch[3])'
         ,@input_data_1 = N'select TOP 10 CustomerID, count(*) as Nof_Orders 
from [Sales].[Orders] GROUP BY CustomerID'
         ,@input_data_1_name = N'WWI_OrdersPerCustomer'
         ,@params = N' @F_Val VARCHAR(1000) OUTPUT, @Sig VARCHAR(1000) OUTPUT'
         ,@F_Val = @F_Value OUTPUT
         ,@Sig = @Signif OUTPUT


SELECT 
       @F_Value AS CHI_Value
      ,@Signif AS CHI_Square_SIGNIFICANCE;
GO</pre>
<p> </p>
<p>With @param and @parameter1 I was able to get two separate values from a list of a statistical test (against some sample data) in one run. Of course, the result of unlist function can be added to data.frame and easier parsed but what if I wanted to have data displayed as a frequencies and also test of statistical significance, I can simply do:</p>
<p> </p>
<pre>USE WideWorldImporters;
GO

DECLARE @F_Value VARCHAR(1000)
DECLARE @Signif VARCHAR(1000)


  EXECUTE sys.sp_execute_external_script
          @language = N'R'
         ,@script = N'mytable &lt;- table(WWI_OrdersPerCustomer$CustomerID, 
WWI_OrdersPerCustomer$Nof_Orders) 
                     data.frame(margin.table(mytable, 2))
                     Ch &lt;- unlist(chisq.test(mytable))
                     F_Val &lt;- as.character(Ch[1])
                     Sig &lt;- as.character(Ch[3])
                     OutputDataSet&lt;-data.frame(margin.table(mytable, 2))'
         ,@input_data_1 = N'select TOP 10 CustomerID, count(*) as Nof_Orders 
from [Sales].[Orders] GROUP BY CustomerID'
         ,@input_data_1_name = N'WWI_OrdersPerCustomer'
         ,@params = N' @F_Val VARCHAR(1000) OUTPUT, @Sig VARCHAR(1000) OUTPUT'
         ,@F_Val = @F_Value OUTPUT 
         ,@Sig = @Signif OUTPUT
 WITH RESULT SETS(
                  (Cust_data INT
                  ,Freq INT)
                  )

SELECT @F_Value AS CHI_Value
    ,@Signif AS CHI_Square_SIGNIFICANCE</pre>
<p> </p>
<p>As you can see, there is result set clauses added and R script has 3 outputs defined; 1 for the data.frame output and 2 variables through parameters for statistical significance; as shown on print-screen:</p>
<p><img alt="Capture" class="wp-image-1863" src="https://i0.wp.com/tomaztsql.files.wordpress.com/2016/08/capture.png?w=456&ssl=1" data-recalc-dims="1"></p>
<p>Such export of the results is always very useful. In Reporting Services, in Power BI or simply in SSMS when running the resulsts.</p>
<p>Code available at <a target="_blank" href="https://github.com/tomaztk/Multiparameters_R_SQL_Server">Github</a>.</p>
<p>Happy R-SQLing!</p>
<p> <a target="_blank" rel="nofollow" href="http://feeds.wordpress.com/1.0/gocomments/tomaztsql.wordpress.com/1428/"><img alt="" src="http://feeds.wordpress.com/1.0/comments/tomaztsql.wordpress.com/1428/" border="0"></a> <img alt="" src="https://i1.wp.com/pixel.wp.com/b.gif?resize=1%2C1&ssl=1" border="0" data-recalc-dims="1"></p>
<p><div><div><div><div data-width="55" data-show-faces="false" data-send="true" data-layout="box_count" data-height="62" data-href="https://www.r-bloggers.com/using-parameter-and-multiparameters-with-sp_execute_external_script/"></div></div><div><a data-url="https://www.r-bloggers.com/using-parameter-and-multiparameters-with-sp_execute_external_script/" data-counturl="https://www.r-bloggers.com/using-parameter-and-multiparameters-with-sp_execute_external_script/" data-count="vertical" data-text="Using parameter and multiparameters with  sp_execute_external_script" class="twitter-share-button" data-via="rbloggers" href="https://twitter.com/share"></a></div><div></div></div><div></div></div>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="https://tomaztsql.wordpress.com/2016/08/01/using-parameter-and-multiparameters-with-sp_execute_external_script/"> R – TomazTsql</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a title="The R Project for Statistical Computing" rel="nofollow" href="https://www.r-project.org/">R</a> news and <a title="R tutorials" rel="nofollow" href="https://www.r-bloggers.com/?s=tutorial">tutorials</a> on topics such as: <a title="Data science" rel="nofollow" href="https://www.r-bloggers.com/?s=data%20science">Data science</a>, <a title="Big Data" rel="nofollow" href="https://www.r-bloggers.com/?s=Big%20Data">Big Data, <a title="R jobs" rel="nofollow" href="https://www.r-users.com/">R jobs</a>, visualization (<a title="ggplot and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/?s=ggplot2">ggplot2</a>, <a title="Boxplots using lattice and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/?s=boxplot">Boxplots</a>, <a title="Maps and gis" rel="nofollow" href="https://www.r-bloggers.com/?s=map">maps</a>, <a title="Animation in R" rel="nofollow" href="https://www.r-bloggers.com/?s=animation">animation</a>), programming (<a title="RStudio IDE for R" rel="nofollow" href="https://www.r-bloggers.com/?s=RStudio">RStudio</a>, <a title="Sweave and literate programming" rel="nofollow" href="https://www.r-bloggers.com/?s=sweave">Sweave</a>, <a title="LaTeX in R" rel="nofollow" href="https://www.r-bloggers.com/?s=LaTeX">LaTeX</a>, <a title="SQL and databases" rel="nofollow" href="https://www.r-bloggers.com/?s=SQL">SQL</a>, <a title="Eclipse IDE for R" rel="nofollow" href="https://www.r-bloggers.com/?s=eclipse">Eclipse</a>, <a title="git and github, Version Control System" rel="nofollow" href="https://www.r-bloggers.com/?s=git">git</a>, <a title="Large data in R using Hadoop" rel="nofollow" href="https://www.r-bloggers.com/?s=hadoop">hadoop</a>, <a title="Web Scraping of google, facebook, yahoo, twitter and more using R" rel="nofollow" href="https://www.r-bloggers.com/?s=Web+Scraping">Web Scraping</a>) statistics (<a title="Regressions and ANOVA analysis tutorials" rel="nofollow" href="https://www.r-bloggers.com/?s=regression">regression</a>, <a title="principal component analysis tutorial" rel="nofollow" href="https://www.r-bloggers.com/?s=PCA">PCA</a>, <a title="Time series" rel="nofollow" href="https://www.r-bloggers.com/?s=time+series">time series</a>, <a title="finance trading" rel="nofollow" href="https://www.r-bloggers.com/?s=trading">trading</a>) and more...
<img height="1" alt="" width="1" src="http://feeds.feedburner.com/~r/RBloggers/~4/EwPL7_WLUkU"></a></div>