Avoid dynamic SQL generation when calling stored procedure on SQL server from R/DBI/ODBC

Question

Using the profiler on SQL Server to monitor a stored procedure call via DBI/odbc, shows that dynamic SQL / prepared statement is generated :

con <- DBI::dbConnect(odbc::odbc(),...)
dbGetQuery(con, "EXEC sp_helptext 'sys.sp_addlogin';")

Results on SQL Server in:

declare @p1 int
set @p1=2
exec sp_prepexec @p1 output,NULL,N'EXEC sp_helptext ''sys.sp_addlogin'';'
select @p1

For database safety & performance we disabled dynamic SQL calls, so that I would like a direct call:

EXEC sp_helptext 'sys.sp_addlogin';

Not sure if the dynamic SQL overlay is generated by DBI or by the odbc driver.
Is there a way to avoid it?

Answer 1

I found what I was looking for in odbc package documentation : direct execution.

The odbc package uses Prepared Statements to compile the query once and reuse it, allowing large or repeated queries to be more efficient. However, prepared statements can actually perform worse in some cases, such as many different small queries that are all only executed once. Because of this the odbc package now also supports direct queries by specifying immediate = TRUE.

This will use a prepared statement:

dbGetQuery(con, "EXEC sp_helptext 'sys.sp_addlogin';")

declare @p1 int
set @p1=2
exec sp_prepexec @p1 output,NULL,N'EXEC sp_helptext ''sys.sp_addlogin'';'
select @p1

This will execute the stored procedure directly:

dbGetQuery(con, "EXEC sp_helptext 'sys.sp_addlogin';",immediate = TRUE)

EXEC sp_helptext 'sys.sp_addlogin';