Passing query as variable to Rmarkdown sql chunk
<p>I'm trying to use the <a href="http://rmarkdown.rstudio.com/authoring_knitr_engines.html" rel="noreferrer">SQL chunk function</a> available in the <a href="https://www.rstudio.com/products/rstudio/download/preview/" rel="noreferrer">preview version of RStudio 1.0</a> to connect to a SQL Server (using the RSQLServer backend for DBI) and I'm having some difficulty passing variables.</p>

<p>If I connect to the server and then put the query in the chunk it works as expected</p>

<pre><code>```{r, eval = F}
svr &lt;- dbConnect(RSQLServer::SQLServer(), "Server_name", database = 'Database_name')
query &lt;- 'SELECT TOP 10 * FROM Database_name.dbo.table_name'
```
```{sql, connection = svr, eval = F}
SELECT TOP 10 * FROM Database_name.dbo.table_name
```
</code></pre>

<p>But if I try to pass the query as a variable it throws an error</p>

<pre><code>```{sql, connection = svr, eval = F}
?query
```

Error: Unable to retrieve JDBC result set for 'SELECT TOP 10 * FROM Database_name.dbo.table_name': Incorrect syntax near 'SELECT TOP 10 * FROM Database_name.dbo.table_name'.
Failed to execute SQL chunk
</code></pre>

<p>I think it's related to the way R wraps character vectors in quotes, because I get the same error if I run the following code.</p>

<pre><code>```{sql, connection = svr, eval = F}
'SELECT TOP 10 * FROM Database_name.dbo.table_name'
```
</code></pre>

<p>Is there a way I can get around this error?</p>

<p>Currently I can achieve what I want by <a href="https://stackoverflow.com/questions/39630279/passing-query-as-variable-to-rmarkdown-sql-chunk">using inline expressions to print the query</a>, <a href="https://github.com/yihui/knitr/issues/1087" rel="noreferrer">using the pygments for highlighting</a> and running the query in a R chunk with DBI commands, so using code chunks would be a bit nicer.</p>