SQL Server 2016 R服务存储过程含单引号参数报错求助

我一看你这个问题就知道是动态SQL拼接时的单引号转义出问题了，咱们一步步来解决：

问题根源 #

你现在的存储过程里，把@param1直接拼到@sql字符串里，当参数里有单引号的时候（比如传入N' Don''t ...'，实际@param1的值是Don't ...），拼接后的SQL语句就会变成：

select = 'Don't do late making Tom's work';

这里的单引号直接打断了字符串，SQL Server自然会报语法错误——它会把Don't里的第二个单引号当成字符串结束标记，后面的ot就成了无效语法。

最优解决方案：避免不必要的动态SQL #

其实你根本不需要构造这个@sql字符串啊！你的需求是把文本参数传给R服务，直接把@param1作为参数传给sp_execute_external_script就行了，完全绕开动态拼接的坑：

CREATE PROCEDURE [dbo].[test] (@param1 NVARCHAR(MAX)) 
AS 
BEGIN 
SET NOCOUNT ON; 

EXEC sp_execute_external_script 
    @language = N'R', 
    @script = N'
        data <- mynumbers
        OutputDataSet <- data.frame(data)
    ', 
    @params = N'@mynumbers nvarchar(max)', 
    @mynumbers = @param1  -- 直接传参数，不用拼接SQL
END

这样执行exec test N' Don''t do late making Tom''s work '绝对不会报错，因为参数是直接传递的，不需要处理单引号转义。

如果确实需要动态SQL（比如有其他业务需求） #

要是你必须构造动态SQL字符串，那一定要用REPLACE函数把参数里的单引号转义成两个单引号（SQL Server里单引号的转义规则就是用两个单引号表示一个），同时注意把@sql的类型改成NVARCHAR(MAX)（和参数类型匹配，避免编码问题）：

CREATE PROCEDURE [dbo].[test] (@param1 NVARCHAR(MAX)) 
AS 
BEGIN 
SET NOCOUNT ON; 
DECLARE @sql NVARCHAR(MAX)  -- 用NVARCHAR匹配参数类型
SET @sql = 'select ''' + REPLACE(@param1, '''', '''''') + ''' as result;' 
PRINT(@sql)  -- 现在打印出来的SQL会是正确转义后的
EXEC sp_execute_external_script 
    @language = N'R', 
    @script = N'
        data <- mynumbers
        OutputDataSet <- data.frame(data)
    ', 
    @params = N'@mynumbers nvarchar(max)', 
    @mynumbers = @sql 
END

比如传入Don't，转义后会变成Don''t，拼接后的SQL就是select 'Don''t' as result;，SQL Server就能正确解析了。

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