R中使用RODBC运行含@的SQL Server脚本报错如何解决？

你遇到的报错和@符号没有关系，核心问题是**GO是SQL Server客户端工具（如SSMS）专用的批处理分隔符，不属于标准T-SQL语法，RODBC的执行接口无法识别该关键字，直接判定为语法错误**。@作为T-SQL声明变量的原生语法符号，不需要额外转义，RODBC会直接将其透传给SQL Server解析。

1. 直接修改SQL语句（最简单方案） #

删除你现有SQL中的所有GO关键字，用分号分隔不同语句段即可，修改后的SQL可以直接在R中调用。
修改后的SQL参考如下：

SET DATEFORMAT dmy;  
DECLARE @VerifyClientID TABLE (firstname varchar(100), middlename varchar(100), lastname varchar(100), dob date, mobile varchar(100), ID int);
INSERT INTO @VerifyClientID (firstname, lastname,  mobile, ID)
VALUES
('JOHN','DOE','0444 444 444',1);
drop table if exists #clientTABLE;
select v.ID, p.ABC_NCMID, P.ABC_RowID, P.ABC_FirstName, P.ABC_GuardianName, P.ABC_LastName, P.ABC_SexCode_ID, P.ABC_CellPhone, P.ABC_DOB, ABC_StreetAddress, ABC_City, ABC_Zip, ABC_SSN, ABC_HomePhone
into #clientTABLE
from @VerifyClientID  V
inner join dbo.DV_Person P on soundex(V.firstname) = soundex(P.ABC_FirstName)
and soundex(V.lastname) = soundex(P.ABC_LastName) 
and (convert(varchar,replace(replace(P.ABC_CELLPHONE,' ',''),0,'')) = convert(varchar,replace(replace(V.mobile,' ',''),0,''))
or convert(varchar,replace(replace(P.ABC_HomePhone,' ',''),0,'')) = convert(varchar,replace(replace(V.mobile,' ',''),0,''))
)
where 1=1
and p.ABC_NCMID in (select Per.PER_CLIENTID from AtlasPublic.View_UODS_Person Per)
and P.ABC_IsPatient = 1;

select distinct ID
,firstname
,middlename
,lastname
,dob
,mobile       
, (SELECT TOP 1 ABC_NCMID FROM #clientTABLE WHERE id = v.id) as MatchedID
, (SELECT TOP 1 convert(varchar, ABC_DOB, 103) FROM #clientTABLE WHERE id = v.id) as MatchedDOB
, (SELECT TOP 1 case when ABC_SexCode_ID = 8089 then 'Male' when ABC_SexCode_ID = 8088 then 'Female' else null end FROM #clientTABLE WHERE id = v.id) as MatchedSEX
, (SELECT TOP 1 ABC_StreetAddress FROM #clientTABLE WHERE id = v.id) as MatchedStreet
, (SELECT TOP 1 ABC_City FROM #clientTABLE WHERE id = v.id) as MatchedCity
, (SELECT TOP 1 ABC_Zip FROM #clientTABLE WHERE id = v.id) as MatchedZip
from @VerifyClientID  V;

2. R端调用示例 #

将修改后的SQL放在R的字符串中，直接用RODBC的sqlQuery函数执行即可：

library(RODBC)
# 建立数据库连接
conn <- odbcConnect("你的ODBC数据源名称", uid="账号", pwd="密码")

# 写入修改后的SQL语句
sql_str <- "
SET DATEFORMAT dmy;  
DECLARE @VerifyClientID TABLE (firstname varchar(100), middlename varchar(100), lastname varchar(100), dob date, mobile varchar(100), ID int);
INSERT INTO @VerifyClientID (firstname, lastname,  mobile, ID)
VALUES
('JOHN','DOE','0444 444 444',1);
drop table if exists #clientTABLE;
select v.ID, p.ABC_NCMID, P.ABC_RowID, P.ABC_FirstName, P.ABC_GuardianName, P.ABC_LastName, P.ABC_SexCode_ID, P.ABC_CellPhone, P.ABC_DOB, ABC_StreetAddress, ABC_City, ABC_Zip, ABC_SSN, ABC_HomePhone
into #clientTABLE
from @VerifyClientID  V
inner join dbo.DV_Person P on soundex(V.firstname) = soundex(P.ABC_FirstName)
and soundex(V.lastname) = soundex(P.ABC_LastName) 
and (convert(varchar,replace(replace(P.ABC_CELLPHONE,' ',''),0,'')) = convert(varchar,replace(replace(V.mobile,' ',''),0,''))
or convert(varchar,replace(replace(P.ABC_HomePhone,' ',''),0,'')) = convert(varchar,replace(replace(V.mobile,' ',''),0,''))
)
where 1=1
and p.ABC_NCMID in (select Per.PER_CLIENTID from AtlasPublic.View_UODS_Person Per)
and P.ABC_IsPatient = 1;

select distinct ID
,firstname
,middlename
,lastname
,dob
,mobile       
, (SELECT TOP 1 ABC_NCMID FROM #clientTABLE WHERE id = v.id) as MatchedID
, (SELECT TOP 1 convert(varchar, ABC_DOB, 103) FROM #clientTABLE WHERE id = v.id) as MatchedDOB
, (SELECT TOP 1 case when ABC_SexCode_ID = 8089 then 'Male' when ABC_SexCode_ID = 8088 then 'Female' else null end FROM #clientTABLE WHERE id = v.id) as MatchedSEX
, (SELECT TOP 1 ABC_StreetAddress FROM #clientTABLE WHERE id = v.id) as MatchedStreet
, (SELECT TOP 1 ABC_City FROM #clientTABLE WHERE id = v.id) as MatchedCity
, (SELECT TOP 1 ABC_Zip FROM #clientTABLE WHERE id = v.id) as MatchedZip
from @VerifyClientID  V;
"

# 执行查询获取结果
result <- sqlQuery(conn, sql_str)

# 关闭连接
odbcClose(conn)

3. 进阶优化方案（动态匹配客户更方便） #

如果你的待匹配客户列表是动态的，不需要每次修改SQL，可以提前在SQL Server中创建存储过程，把待匹配的客户数据作为参数传入，R端仅需要调用存储过程传递参数即可，代码更简洁，也避免了SQL拼接的语法风险。

内容的提问来源于stack exchange，提问作者Banji