title: R query database tables iteratively without for loop with lambda or vectorized function for Shiny app
body: <p>I am connecting to a SQL Server database through the ODBC connection in R. I have two potential methods to get data, and am trying to determine which would be more efficient. The data is needed for a Shiny dashboard, so the data needs to be pulled while the app is loading rather than querying on the fly as the user is using the app.</p>
  <p>Method 1 is to use over 20 stored procedures to query all of the needed data and store them for use. Method 2 is to query all of the tables individually.</p>
  <p>Here is the method I used to query one of the stored procedures:</p>
  <pre><code>get_proc_data &lt;- function(proc_name, url, start_date, end_date){
    dbGetQuery(con, paste0(
      &quot;EXEC dbo.&quot;, proc_name, &quot; &quot;,
      &quot;@URL = N'&quot;, url, &quot;', &quot;,
      &quot;@Startdate = '&quot;, start_date, &quot;', &quot;,
      &quot;@enddate = '&quot;, end_date, &quot;' &quot;
    ))
  }
  data &lt;- get_proc_data(proc_name, url, today(), today() %m-% years(5))
  </code></pre>
  <p>However, each of the stored procedures has a slightly different setup for the parameters, so I would have to define each of them separately.</p>
  <p>I have started to implement Method 2, but have run into issues with iteratively querying each table.</p>
  <pre><code># use dplyr create list of table names
  db_tables &lt;- dbGetQuery(con, &quot;SELECT * FROM [database_name].INFORMATION_SCHEMA.TABLES;&quot;) %&gt;% select(TABLE_NAME)
  # use dplyr pull to create list
  table_list &lt;- pull(db_tables , TABLE_NAME)
  
  # get a quick look at the first few rows 
  tbl(con, &quot;[TableName]&quot;) %&gt;% head() %&gt;% glimpse()
  
  # iterate through all table names, get the first five rows, and export to .csv
  for (table in table_list){
      write.csv(
          tbl(con, table) %&gt;% head(), str_glue(&quot;{getwd()}/00_exports/tables/{table}.csv&quot;)
      )
  }
  
  selected_tables &lt;- db_tables %&gt;% filter(TABLE_NAME == c(&quot;TableName1&quot;,&quot;TableName2&quot;))
  </code></pre>
  <p>Ultimately this method was just to test how long it would take to iterate through the ~60 tables and perform the required function. I have tried putting this into a function instead but have not been able to get it to iterate through while also pulling the name of the table.</p>
  <p>Pro/Con for Method 1:  The stored procs are currently powering a metrics plug-in that was written in C++ and is displaying metrics on the webpage. This is for internal use to monitor website performance. However, the stored procedures are not all visible to me and the client needs me to extend their current metrics. I also do not have a DBA at my disposal to help with the SQL Server side, and the person who wrote the procs is unavailable. The procs are also using different logic than each other, so joining the results of two different procs gives drastically different values. For example, depending on the proc, each date will list total page views for each day or already be aggregated at the weekly or monthly scale then listed repeatedly. So joining and grouping causes drastic errors in actual page views.</p>
  <p>Pro/Con for Method 2:  I am familiar with dplyr and would be able to join the tables together to pull the data I need. However, I am not as familiar with SQL and there is no Entity-Relationship Diagram (ERD) of any sort to refer to. Otherwise, I would build each query individually.</p>
  <p>Either way, I am trying to come up with a way to proceed with either a named function, lambda function, or vectorized method for iterating. It would be great to name each variable and assign them appropriately so that I can perform the data wrangling with dplyr.</p>
  <p>Any help would be appreciated, I am overwhelmed with which direction to go. I researched the equivalent to Python list comprehension in R but have not been able get the function in R to perform similarly.</p>
  <pre><code>&gt; db_table_head_to_csv &lt;- function(table) {
  +   write.csv(
  +     tbl(con, table) %&gt;% head(), str_glue(&quot;{getwd()}/00_exports/bibliometrics_tables/{table}.csv&quot;)
  +   )
  + }
  &gt; 
  &gt; bibliometrics_tables %&gt;% db_table_head_to_csv()
   Error in UseMethod(&quot;as.sql&quot;) : 
  no applicable method for 'as.sql' applied to an object of class &quot;data.frame&quot;
  </code></pre>