如何在R中写入功能，该功能给出与以下存储过程相同的输出

Question

我在sql server中有一个存储过程，我在R中写了它。

ALTERProc [dbo].[UPD_CREDIT]          
AS
BEGIN
    DECLARE @cmd VARCHAR(1000)
    DECLARE @ssispath VARCHAR(1000)
    SET @ssispath = 'H:RMS PKGUPD_CREDIT.dtsx'
    SELECT @cmd = 'dtexec /F "'+ @ssispath+'"'
    SELECT @cmd = @cmd
    exec master..xp_cmdshell @cmd
    if exists(select top 2 *, task = (select TaskName from Table1 A         
where A.id = B.id and A.taskid = B.taskid)
              from Table2 B 
              where id = 5        
                and taskstatus = 'N'orderby taskrunID desc)
    begin
        RAISERROR('THERE IS AN ERROR IN EXECUTIoN ', 16, 1);
    end

我的R功能是

UPD_CREDIT <- function(){
  Table1 <- dbSendQuery(conn, 'Select * from Table1') 
  Table2 <- dbSendQuery(conn, 'Select * from Table2')
  func1 <- function(Table1, Table2){
  Table2 <- Table2 %>% filter(id = 5, taskstatus == 'N') %>% arrange(desc(taskrunID))
  task <- merge(x = Table1,y = Table2 ,by =c("id","taskid"))
  if (nrow(task) >2) {
    res = TRUE }
    res = FALSE
    return(res) }
  res <- func1(Table1, Table2)
  if(res){print('THERE IS AN ERROR IN EXECUTIoN')}   
}

我无法获得此部分，如何在r

中执行这些部分

declare @cmdvarchar(1000)
    declare @ssispathvarchar(1000)
    set @ssispath='H:RMS PKGUPD_CREDIT.dtsx'
    select @cmd='dtexec /F "'+ @ssispath+'"'
    select @cmd= @cmd
    execmaster..xp_cmdshell@cmd

Answer 1

本质上，这些行将值传递到命令行shell调用到SSIS dtexec实用程序，R可以使用system()类似地运行。

这是假设r在同一环境（访问H驱动器对象）的同一用户或特权以调用存储过程中运行。可能此实用程序更新了同一连接数据库的 table1 和 table2 。另外，您可以使用JOIN直接在数据库中运行merge并避免导入两个表客户端。

# COMMAND LINE CALL
ssispath <- 'H:\RMS PKG\UPD_CREDIT.dtsx'
cmd <- paste0('dtexec /F "', @ssispath, '"')    
system(cmd)
UPD_CREDIT <- function() {
    # SQL SERVER QUERY
    task <- dbSendQuery(conn, paste("SELECT *", 
                                    "FROM Table1 t1",
                                    "INNER JOIN Table2 t2",
                                    "  ON  t1.id = t2.id AND t1.taskid = t2.taskid",  
                                    "  AND t2.id = 5 AND t2.taskstatus = 'N'")
                       )
    if(nrow(task) > 2) print("THERE IS AN ERROR IN EXECUTION")
}