<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/some-quirks-with-r-and-sql-server-by-ellis2013nz/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="http://ellisp.github.io/blog/2017/12/09/sql-and-r"> Peter's stats stuff - R</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>I’ve been writing on this blog less frequently in the past few months. Mostly this is because I’ve been working on a very intensive and engaging professional project that is much more hands-on (ie coding) than I’ve been at work for a while. So a lot of energy has been going into that. One interesting side effect for me has been diving deep into Structured Query Language (SQL), and the Microsoft-flavoured <a rel="nofollow" href="https://en.wikipedia.org/wiki/Transact-SQL" target="_blank">Transact SQL</a> in particular. I’ve used SQL for a long time, but usually with the attitude of “get it out of the database as quickly as possible”. In a situation where this didn’t make sense, I’ve been pleasantly surprised at how powerful and flexible SQL is for the right sort of jobs.</p>
<p>The bulk of the work project is in SQL, but there is an R flavour with a Shiny front end and a bunch of testing and semi-automation of the build process going on in R (long story). Here are a couple of quirky and useful things relating to using R and SQL Server in combination.</p>
<h2>Using R to schedule SQL scripts</h2>
<p>Imagine a project that combines SQL and R. For example, SQL is used to do a bunch of heavy lifting data management and complex queries in the database; and R is used for statistical modelling and producing polished outputs. This is actually a very common scenario. Relational databases are very powerful tools with many decades of optimisation embedded in them. They aren’t going anywhere soon.</p>
<blockquote>
<p>“Whether your first data analysis language is R, Python, Julia or SAS, your <em>second</em> language should be SQL”</p>
</blockquote>
<p><em>Quote by me – something I just thought of.</em></p>
<p>It’s good practice to keep any non-trivial SQL queries in their own files with a .sql suffix, and develop them in a database-aware environment. With SQL Server, this will often mean SQL Server Management Studio. But when you come to doing the final stage of reproducible analysis, you don’t want to be flicking between two applications; certainly not in terms of actually <em>running</em> anything. Although SQL Server since 2016 can include R code in a stored procedure, if it’s basically a statistical project it’s still going to have the workflow of “database first, R second”, with the statistical and presentation stage probably developed in RStudio or similar. So it’s very useful to be able to run bunch of .sql scripts from R. This is commonly done by reading in the script with <code>readLines()</code> and executing it on the database via RODBC or other database connection software.</p>
<p>I developed an R function <code>sql_execute()</code> to make this process efficient. The version below is available in my <a rel="nofollow" href="https://github.com/ellisp/pssmisc-r-package" target="_blank"><code>pssmisc</code> R package</a> (only on GitHub at this point) which is a grab-bag of multi-use functionality associated with this blog. The <a rel="nofollow" href="https://github.com/StatisticsNZ/population-explorer/blob/master/build-db/00-src/sql_execute.R" target="_blank">original version</a> had a few project-specific features as well as a few cut corners. It also had an accompanying simple function that uses <code>sql_execute()</code> to run all the SQL scripts in a given folder in order.</p>
<p>The <code>sql_execute()</code> function provides the following benefits:</p>
<ul>
<li>combines the multiple steps of reading in SQL scripts and executing them in a single command</li>
<li>handles a common problem where files developed in Management Studio often aren’t saved in an encoding automatically recognised by R</li>
<li>allows the use of the <code>GO</code> batch separator, a Microsoft-specific addition to SQL that will cause problems if included in a ODBC query</li>
<li>lets you specify a search-and-replace for a string – very useful sometimes if you’re running lots of SQL scripts to be able to say something like “oh by the way, can you change all references to database X to database Y while you’re at it”</li>
<li>lets you specify if an error in one batch should be fatal, or whether to proceed with the rest of the batches from that script</li>
<li>logs execution and results in a table in the databse.</li>
</ul>
<p>Dealing with <code>GO</code> was particularly important, because it’s common (and often essential) in T-SQL development. <code>GO</code> divides a single script into batches. The <code>sql_execute()</code> function below embraces this, by splitting the original file into separate queries based on the location of <code>GO</code>, and sending the individual batches one at a time to the server.</p>
<figure>
<pre><code data-lang="r"><span># Helper function to convert the output of Sys.time() into a character string
# without the time zone on it
# # @details Not exported.
# @keywords internal
# @param dt an object of class \code{POSIXCT}
# @examples
# datetime_ch(Sys.time())
</span><span>datetime_ch</span><span> </span><span>&lt;-</span><span> </span><span>function</span><span>(</span><span>dt</span><span>){</span><span>
</span><span>dt</span><span> </span><span>&lt;-</span><span> </span><span>gsub</span><span>(</span><span>&quot; [A-Z]*$&quot;</span><span>,</span><span> </span><span>&quot;&quot;</span><span>,</span><span> </span><span>as.character</span><span>(</span><span>dt</span><span>))</span><span>
</span><span>dt</span><span> </span><span>&lt;-</span><span> </span><span>paste0</span><span>(</span><span>&quot;CAST ('&quot;</span><span>,</span><span> </span><span>dt</span><span>,</span><span> </span><span>&quot;' AS DATETIME)&quot;</span><span>)</span><span>
</span><span>return</span><span>(</span><span>dt</span><span>)</span><span>
</span><span>}</span><span>
</span><span>#' Execute SQL
#'
#' Execute T-SQL in a script, split into batches
#' #' @export
#' @importFrom RODBC sqlQuery
#' @importFrom stringr str_split str_length #' @details Reads a script of SQL, splits it into separate queries on the basis of any occurrences of \code{GO}
#' in the script, and passes it to the database server for execution. While the initial use case was for SQL Server, there's no
#' reason why it wouldn't work with other ODBC connections.
#' #' The case of \code{GO} is ignored but it has to be the first non-space word on its line of code.
#' #' If any batch at any point returns rows of data (eg via a \code{SELECT} statement that does not \code{INSERT} the
#' results into another table or variable on the database), the rest of that batch is not executed. #' If that batch was the last batch of SQL
#' in the original file, the results are returned as a data.frame, otherwise it is discarded.
#' #' Example SQL code for creating a log suitable for this function:
#' \preformatted{
#' CREATE TABLE some_database.dbo.sql_executed_by_r_log
#' (
#' log_event_code INT NOT NULL IDENTITY PRIMARY KEY, #' start_time DATETIME, #' end_time DATETIME,
#' sub_text NVARCHAR(200),
#' script_name NVARCHAR(1000),
#' batch_number INT,
#' result NCHAR(30),
#' err_mess VARCHAR(8000),
#' duration NUMERIC(18, 2)
#' );
#' }
#' #' @param channel connection handle as returned by RODBC::odbcConnect() of class RODBC
#' @param filename file name of an SQL script
#' @param sub_in character string that you want to be replaced with \code{sub_out}. Useful if you want to do a bulk search
#' and replace. This is useful if you have a bunch of scripts that you maybe want
#' to run on one schema sometimes, and on another schema other times - just automate the search and replace. Use with caution.
#' @param sub_out character string that you want to replace \code{sub_in} with.
#' @param fixed logical. If TRUE, \code{sub_in} is a string to be matched as is. Otherwise it is treated as a regular expression #' (eg if fixed = FALSE, then . is a wild card)
#' @param error_action should you stop with an error if a batch gets an error message back from the database? Any alternative
#' to &quot;stop&quot; means we just keep ploughing on, which may or may not be a bad idea. Use &quot;stop&quot; unless you know that failure
#' in one part of a script isn't fatal.
#' @param log_table table in the database to record a log of what happened. Set to NULL if no log table available. The log_table
#' needs to have (at least) the following columns: event_time, sub_out, script_name, batch_number, result, err_mess and duration. #' See Details for example SQL to create such a log table.
#' @param verbose Logical, gives some control over messages
#' @param ... other arguments to be passed to \code{sqlQuery()}, such as \code{stringsAsFactors = FALSE}.
#' @examples
#' \dontrun{
#' ch &lt;-odbcConnect(&quot;some_dsn&quot;)
#' sql_execute(ch, &quot;some_file.sql&quot;, log_table = &quot;some_database.dbo.sql_executed_by_r_log&quot;)
#' }
#' @author Peter Ellis
</span><span>sql_execute</span><span> </span><span>&lt;-</span><span> </span><span>function</span><span>(</span><span>channel</span><span>,</span><span> </span><span>filename</span><span>,</span><span> </span><span>sub_in</span><span> </span><span>=</span><span> </span><span>NULL</span><span>,</span><span> </span><span>sub_out</span><span> </span><span>=</span><span> </span><span>NULL</span><span>,</span><span> </span><span>fixed</span><span> </span><span>=</span><span> </span><span>TRUE</span><span>,</span><span> </span><span>error_action</span><span> </span><span>=</span><span> </span><span>&quot;stop&quot;</span><span>,</span><span> </span><span>log_table</span><span> </span><span>=</span><span> </span><span>NULL</span><span>,</span><span> </span><span>verbose</span><span> </span><span>=</span><span> </span><span>TRUE</span><span>,</span><span> </span><span>...</span><span>){</span><span>
</span><span># we can't tell in advance what encoding the .sql files are in, so we read it in
</span><span> </span><span># in two ways (one of which is certain to return gibberish) and choose the version that is recognised as a proper string:
</span><span> </span><span># encoding method 1 (weird Windows encoding):
</span><span> </span><span>file_con</span><span> </span><span>&lt;-</span><span> </span><span>file</span><span>(</span><span>filename</span><span>,</span><span> </span><span>encoding</span><span> </span><span>=</span><span> </span><span>&quot;UCS-2LE&quot;</span><span>)</span><span>
</span><span>sql1</span><span> </span><span>&lt;-</span><span> </span><span>paste</span><span>(</span><span>readLines</span><span>(</span><span>file_con</span><span>,</span><span> </span><span>warn</span><span> </span><span>=</span><span> </span><span>FALSE</span><span>),</span><span> </span><span>collapse</span><span> </span><span>=</span><span> </span><span>&quot;\n&quot;</span><span>)</span><span>
</span><span>close</span><span>(</span><span>file_con</span><span>)</span><span>
</span><span># encoding method 2 (let R work it out - works in most cases):
</span><span> </span><span>file_con</span><span> </span><span>&lt;-</span><span> </span><span>file</span><span>(</span><span>filename</span><span>)</span><span>
</span><span>sql2</span><span> </span><span>&lt;-</span><span> </span><span>paste</span><span>(</span><span>readLines</span><span>(</span><span>file_con</span><span>,</span><span> </span><span>warn</span><span> </span><span>=</span><span> </span><span>FALSE</span><span>),</span><span> </span><span>collapse</span><span> </span><span>=</span><span> </span><span>&quot;\n&quot;</span><span>)</span><span>
</span><span>close</span><span>(</span><span>file_con</span><span>)</span><span>
</span><span># choose between the two encodings, based on which one has a legitimate string length:
</span><span> </span><span>suppressWarnings</span><span>({</span><span>
</span><span>if</span><span>(</span><span>is.na</span><span>(</span><span>stringr</span><span>::</span><span>str_length</span><span>(</span><span>sql2</span><span>))){</span><span>
</span><span>sql</span><span> </span><span>&lt;-</span><span> </span><span>sql1</span><span>
</span><span>}</span><span> </span><span>else</span><span> </span><span>{</span><span>
</span><span>sql</span><span> </span><span>&lt;-</span><span> </span><span>sql2</span><span>
</span><span>}</span><span>
</span><span>})</span><span>
</span><span># do the find and replace that are needed
</span><span> </span><span>if</span><span>(</span><span>!</span><span>is.null</span><span>(</span><span>sub_in</span><span>)){</span><span>
</span><span>sql</span><span> </span><span>&lt;-</span><span> </span><span>gsub</span><span>(</span><span>sub_in</span><span>,</span><span> </span><span>sub_out</span><span>,</span><span> </span><span>sql</span><span>,</span><span> </span><span>fixed</span><span> </span><span>=</span><span> </span><span>fixed</span><span>)</span><span>
</span><span>}</span><span>
</span><span># split the SQL into separate commands wherever there is a &quot;GO&quot; at the beginning of a line
</span><span> </span><span># (&quot;GO&quot; is not ANSI SQL, only works for SQL Server - it indicates the lines above are a batch)
</span><span> </span><span>sql_split</span><span> </span><span>&lt;-</span><span> </span><span>stringr</span><span>::</span><span>str_split</span><span>(</span><span>sql</span><span>,</span><span> </span><span>&quot;\\n *[Gg][Oo]&quot;</span><span>,</span><span> </span><span>simplify</span><span> </span><span>=</span><span> </span><span>TRUE</span><span>)</span><span>
</span><span>base_log_entry</span><span> </span><span>&lt;-</span><span> </span><span>data.frame</span><span>(</span><span>
</span><span>sub_out</span><span> </span><span>=</span><span> </span><span>ifelse</span><span>(</span><span>is.null</span><span>(</span><span>sub_out</span><span>),</span><span> </span><span>&quot;none&quot;</span><span>,</span><span> </span><span>sub_out</span><span>),</span><span>
</span><span>script_name</span><span> </span><span>=</span><span> </span><span>filename</span><span>,</span><span>
</span><span>stringsAsFactors</span><span> </span><span>=</span><span> </span><span>FALSE</span><span>
</span><span>)</span><span>
</span><span>n_batches</span><span> </span><span>&lt;-</span><span> </span><span>length</span><span>(</span><span>sql_split</span><span>)</span><span>
</span><span># execute the various separate commands
</span><span> </span><span>for</span><span>(</span><span>i</span><span> </span><span>in</span><span> </span><span>1</span><span>:</span><span>n_batches</span><span>){</span><span>
</span><span>log_entry</span><span> </span><span>&lt;-</span><span> </span><span>base_log_entry</span><span>
</span><span>log_entry</span><span>$</span><span>batch_number</span><span> </span><span>&lt;-</span><span> </span><span>i</span><span>
</span><span>log_entry</span><span>$</span><span>result</span><span> </span><span>&lt;-</span><span> </span><span>&quot;no error&quot;</span><span>
</span><span>log_entry</span><span>$</span><span>err_mess</span><span> </span><span>&lt;-</span><span> </span><span>&quot;&quot;</span><span>
</span><span>log_entry</span><span>$</span><span>start_time</span><span> </span><span>&lt;-</span><span> </span><span>datetime_ch</span><span>(</span><span>Sys.time</span><span>())</span><span>
</span><span>if</span><span>(</span><span>verbose</span><span>){</span><span>message</span><span>(</span><span>paste</span><span>(</span><span>&quot;Executing batch&quot;</span><span>,</span><span> </span><span>i</span><span>,</span><span> </span><span>&quot;of&quot;</span><span>,</span><span> </span><span>n_batches</span><span>))}</span><span>
</span><span>duration</span><span> </span><span>&lt;-</span><span> </span><span>system.time</span><span>({</span><span>res</span><span> </span><span>&lt;-</span><span> </span><span>sqlQuery</span><span>(</span><span>channel</span><span>,</span><span> </span><span>sql_split</span><span>[[</span><span>i</span><span>]],</span><span> </span><span>...</span><span>)})</span><span>
</span><span>log_entry</span><span>$</span><span>duration</span><span> </span><span>&lt;-</span><span> </span><span>duration</span><span>[</span><span>3</span><span>]</span><span>
</span><span>if</span><span>(</span><span>class</span><span>(</span><span>res</span><span>)</span><span> </span><span>==</span><span> </span><span>&quot;data.frame&quot;</span><span>){</span><span>
</span><span>txt</span><span> </span><span>&lt;-</span><span> </span><span>paste</span><span>(</span><span>&quot;Downloaded a data.frame with&quot;</span><span>,</span><span> </span><span>nrow</span><span>(</span><span>res</span><span>),</span><span> </span><span>&quot;rows and&quot;</span><span>,</span><span>
</span><span>ncol</span><span>(</span><span>res</span><span>),</span><span> </span><span>&quot;columns in batch&quot;</span><span>,</span><span> </span><span>i</span><span>,</span><span> </span><span>&quot;. Any commands left in batch&quot;</span><span>,</span><span> </span><span>i</span><span>,</span><span> </span><span>&quot;were not run.&quot;</span><span>)</span><span>
</span><span>if</span><span>(</span><span>verbose</span><span>){</span><span>message</span><span>(</span><span>txt</span><span>)}</span><span>
</span><span>log_entry</span><span>$</span><span>result</span><span> </span><span>&lt;-</span><span> </span><span>&quot;data.frame&quot;</span><span>
</span><span>}</span><span> </span><span>if</span><span>(</span><span>class</span><span>(</span><span>res</span><span>)</span><span> </span><span>==</span><span> </span><span>&quot;character&quot;</span><span> </span><span>&amp;</span><span> </span><span>length</span><span>(</span><span>res</span><span>)</span><span> </span><span>></span><span> </span><span>0</span><span>){</span><span>
</span><span>message</span><span>(</span><span>&quot;\n\nI got this error message:&quot;</span><span>)</span><span>
</span><span>cat</span><span>(</span><span>res</span><span>)</span><span>
</span><span>log_entry</span><span>$</span><span>result</span><span> </span><span>&lt;-</span><span> </span><span>&quot;error&quot;</span><span>
</span><span>log_entry</span><span>$</span><span>err_mess</span><span> </span><span>&lt;-</span><span> </span><span>paste</span><span>(</span><span>gsub</span><span>(</span><span>&quot;'&quot;</span><span>,</span><span> </span><span>&quot;&quot;</span><span>,</span><span> </span><span>res</span><span>),</span><span> </span><span>collapse</span><span> </span><span>=</span><span> </span><span>&quot;\n&quot;</span><span>)</span><span>
</span><span>message</span><span>(</span><span>paste0</span><span>(</span><span>&quot;\n\nSomething went wrong with the SQL execution of batch &quot;</span><span>,</span><span> </span><span>i</span><span>,</span><span> </span><span>&quot; in &quot;</span><span>,</span><span> </span><span>filename</span><span>,</span><span> </span><span>&quot;. \n\nError message from the database is shown above\n\n&quot;</span><span>))</span><span>
</span><span>}</span><span>
</span><span>log_entry</span><span>$</span><span>end_time</span><span> </span><span>&lt;-</span><span> </span><span>datetime_ch</span><span>(</span><span>Sys.time</span><span>())</span><span>
</span><span># Update the log in the database, if we have been given one:
</span><span> </span><span>if</span><span>(</span><span>!</span><span>is.null</span><span>(</span><span>log_table</span><span>)){</span><span>
</span><span># couldn't get sqlSave to append to a table even when append = TRUE... </span><span> </span><span># see https://stackoverflow.com/questions/36913664/rodbc-error-sqlsave-unable-to-append-to-table
</span><span> </span><span># so am writing the SQL to update the log by hand:
</span><span> </span><span>sql</span><span> </span><span>&lt;-</span><span> </span><span>with</span><span>(</span><span>log_entry</span><span>,</span><span> </span><span>paste0</span><span>(</span><span>&quot;INSERT INTO &quot;</span><span>,</span><span> </span><span>log_table</span><span>,</span><span> </span><span>&quot;(start_time, end_time, sub_out, script_name, batch_number, result, err_mess, duration)&quot;</span><span>,</span><span>
</span><span>&quot; VALUES (&quot;</span><span>,</span><span> </span><span>start_time</span><span>,</span><span> </span><span>&quot;, &quot;</span><span>,</span><span> </span><span>end_time</span><span>,</span><span> </span><span>&quot;, '&quot;</span><span>,</span><span> </span><span>sub_out</span><span>,</span><span> </span><span>&quot;', '&quot;</span><span>,</span><span> </span><span>script_name</span><span>,</span><span> </span><span>&quot;', &quot;</span><span>,</span><span> </span><span>batch_number</span><span>,</span><span> </span><span>&quot;, '&quot;</span><span>,</span><span> </span><span>result</span><span>,</span><span> </span><span>&quot;', '&quot;</span><span>,</span><span>
</span><span>err_mess</span><span>,</span><span> </span><span>&quot;', &quot;</span><span>,</span><span> </span><span>duration</span><span>,</span><span> </span><span>&quot;);&quot;</span><span>))</span><span>
</span><span>log_res</span><span> </span><span>&lt;-</span><span> </span><span>sqlQuery</span><span>(</span><span>channel</span><span>,</span><span> </span><span>sql</span><span>)</span><span>
</span><span>}</span><span>
</span><span>if</span><span>(</span><span>error_action</span><span> </span><span>==</span><span> </span><span>&quot;stop&quot;</span><span> </span><span>&amp;&amp;</span><span> </span><span>log_entry</span><span>$</span><span>result</span><span> </span><span>==</span><span> </span><span>&quot;error&quot;</span><span>){</span><span>
</span><span>stop</span><span>(</span><span>paste</span><span>(</span><span>&quot;Stopping due to an error in&quot;</span><span>,</span><span> </span><span>filename</span><span>))</span><span>
</span><span>}</span><span>
</span><span>if</span><span>(</span><span>class</span><span>(</span><span>res</span><span>)</span><span> </span><span>==</span><span> </span><span>&quot;data.frame&quot;</span><span>){</span><span>
</span><span>if</span><span>(</span><span>i</span><span> </span><span>==</span><span> </span><span>n_batches</span><span>){</span><span>
</span><span>return</span><span>(</span><span>res</span><span>)</span><span> </span><span>}</span><span> </span><span>else</span><span> </span><span>{</span><span>
</span><span>warning</span><span>(</span><span>&quot;Downloaded a data frame from batch &quot;</span><span>,</span><span> </span><span>i</span><span>,</span><span> </span><span>&quot; of SQL, which wasn't the \nlast batch in the file. This data frame is not kept.&quot;</span><span>)</span><span>
</span><span>}</span><span>
</span><span>}</span><span>
</span><span>}</span><span>
</span><span>}</span></code></pre>
</figure>
<p>One of the things to watch out for in this situation is how running a script via ODBC can get different results from hitting F5 in Management Studio. One key thing to trip up on is what happens if the SQL includes a <code>SELECT</code> statement that doesn’t <code>INSERT</code> the results into another table or variable, but returns them as a table. In this case, ODBC considers its work done and will not continue to execute anything else in the batch beyond that <code>SELECT</code> statement.</p>
<p>To clarify how this works, here is a potentially problematic SQL file:</p>
<figure>
<pre><code data-lang="sql"><span>/*
eg-sql.sql
for testing the sql_execute R function
*/</span>

<span>-- this will return five rows but sql_execute discards them</span>
<span>SELECT</span> <span>TOP</span> <span>5</span> <span>*</span> <span>FROM</span> <span>some_table</span>
<span>GO</span>

<span>-- this will return an error</span>
<span>some</span> <span>non</span><span>-</span><span>legitimate</span> <span>SQL</span> <span>here</span> <span>that</span> <span>causes</span> <span>an</span> <span>error</span>
<span>go</span>

<span>-- next batch will only get as far as the first seven rows</span>
<span>SELECT</span> <span>TOP</span> <span>7</span> <span>*</span> <span>FROM</span> <span>some_table</span>
<span>SELECT</span> <span>TOP</span> <span>10</span> <span>*</span> <span>FROM</span> <span>some_table</span>
<span>GO</span></code></pre>
</figure>
<p>If I run that file via <code>sql_execute(ch, &quot;examples/eg-sql.sql&quot;)</code>, it does the following:</p>
<ul>
<li>executes the <code>SELECT TOP 5</code> statement and returns the results as a data frame, which is discarded as it is not the result of the last batch of the script</li>
<li>tries to execute the <code>some non-legitimate SQL</code>, gets an error and stops.</li>
</ul>
<p>Alternatively, if I run it via <code>sql_execute(ch, &quot;examples/eg-sql.sql&quot;, error_action = &quot;continue&quot;)</code> it does the following</p>
<ul>
<li>executes the <code>SELECT TOP 5</code> statement and returns the results as a data frame, which is discarded as it is not the result of the last batch of the script</li>
<li>tries to execute the <code>some non-legitimate SQL</code>, gets an error and prints it to the screen.</li>
<li>executes the <code>SELECT TOP 7</code> statement, returns the results as a data frame, and stops. The <code>SELECT TOP 10</code> statement isn’t returned.</li>
</ul>
<h2>An odd quirk with SQL loops cutting short with ODBC</h2>
<p>A second quirk that had me puzzled for a while (and indeed I am still puzzled and can’t get a fully reproducible example) seems to relate to the use of SQL <code>WHILE</code> loops in a script executed on the database from R via ODBC. I found many such SQL programs would silently stop after about 20 iterations of the loop under ODBC, even if they worked perfectly in Management Studio. The examples all look like this:</p>
<figure>
<pre><code data-lang="sql"><span>DECLARE</span> <span>@</span><span>i</span> <span>INT</span> <span>=</span> <span>1</span>
<span>WHILE</span> <span>@</span><span>i</span> <span>&lt;=</span> <span>50</span>
<span>BEGIN</span>
	<span>-- do something that needs looping</span>
	
	
	<span>SET</span> <span>@</span><span>i</span> <span>=</span> <span>@</span><span>i</span> <span>+</span> <span>1</span>
<span>END</span>
<span>GO</span></code></pre>
</figure>
<p>BTW, SQL is one of those languages where you avoid loops if you can, and think instead in terms of joining, aggregating and filtering tables. But there are times when it is necessary (for example, performing an action on each table in a database, such as creating a carefully chosen random sample of it in another database – one of the things we had to do in the work project mentioned above).</p>
<p>The solution to this mysterious refusal to go beyond about 20 (it varied) iterations in some loops was to wrap the whole action in a user-defined stored procedure, then execute the procedure. This seems satisfyingly secure in all sorts of ways. The procedure can be kept permanently or blown away depending on what makes sense:</p>
<figure>
<pre><code data-lang="sql"><span>CREATE</span> <span>PROCEDURE</span> <span>do_stuff</span>
<span>AS</span> 
<span>BEGIN</span>
	<span>DECLARE</span> <span>@</span><span>i</span> <span>INT</span> <span>=</span> <span>1</span>
	<span>WHILE</span> <span>@</span><span>i</span> <span>&lt;=</span> <span>50</span>
	<span>BEGIN</span>
		<span>-- do something that needs looping</span>
	
	
		<span>SET</span> <span>@</span><span>i</span> <span>=</span> <span>@</span><span>i</span> <span>+</span> <span>1</span>
	<span>END</span>
<span>END</span>
<span>GO</span>

<span>EXECUTE</span> <span>do_stuff</span>
<span>DROP</span> <span>PROCEDURE</span> <span>do_stuff</span></code></pre>
</figure>
<p>Worth noting – T-SQL distinguishes between its functions and stored procedures, whereas R lumps the two types of functionality together. Functions in SQL are true computer-science-defined functions, that take inputs and return outputs, with strictly no side effects. Stored procedures can have side effects (like creating or modifying tables). In R, functions can have side effects (and frequently do eg drawing plots), not just return outputs based on inputs.</p>
<p>No graphic today…</p>
	<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="http://ellisp.github.io/blog/2017/12/09/sql-and-r"> Peter's stats stuff - R</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 rel="nofollow" href="https://www.r-project.org/" title="The R Project for Statistical Computing">R</a> news and <a rel="nofollow" href="https://www.r-bloggers.com/search/tutorial" title="R tutorials">tutorials</a> on topics such as: <a rel="nofollow" href="https://www.r-bloggers.com/search/data%20science" title="Data science">Data science</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data" title="Big Data">Big Data, <a rel="nofollow" href="https://www.r-users.com/" title="R jobs">R jobs</a>, visualization (<a rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2" title="ggplot and ggplot2 tutorials">ggplot2</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/boxplot" title="Boxplots using lattice and ggplot2 tutorials">Boxplots</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/map" title="Maps and gis">maps</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/animation" title="Animation in R">animation</a>), programming (<a rel="nofollow" href="https://www.r-bloggers.com/search/RStudio" title="RStudio IDE for R">RStudio</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/sweave" title="Sweave and literate programming">Sweave</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX" title="LaTeX in R">LaTeX</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/SQL" title="SQL and databases">SQL</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/eclipse" title="Eclipse IDE for R">Eclipse</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/git" title="git and github, Version Control System">git</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/hadoop" title="Large data in R using Hadoop">hadoop</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping" title="Web Scraping of google, facebook, yahoo, twitter and more using R">Web Scraping</a>) statistics (<a rel="nofollow" href="https://www.r-bloggers.com/search/regression" title="Regressions and ANOVA analysis tutorials">regression</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/PCA" title="principal component analysis tutorial">PCA</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/time+series" title="Time series">time series</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/trading" title="finance trading">trading</a>) and more...
<img width="1" alt="" src="http://feeds.feedburner.com/~r/RBloggers/~4/93kC8AbfRoo" height="1"></a></div>