SQL动态查询字符串在变量包含单引号时中断

Question

我有一个类似这样的SQL查询字符串：

DECLARE @sql varchar(max)
SET @sql = ' INSERT INTO ' + @tempTable1 + 
           ' SELECT 0 as Type1, 0 as Type2, ' + 
             '''' + @name + ''' as CompanyName ' + 
           ' FROM #tempTable2 tt2'

查询正常运行，但有两个名称包含单引号（例如：Pete's Corner）。当这些名称中的任何一个成为查询的一部分时，它会破坏查询字符串。我认为最简单的方法是像这样替换单引号 replace(@name,'''','') ，但它不起作用，因为我已经在一个字符串中，所以它影响了语句的其余部分。不幸的是，修改表本身不是一个选项。

如何替换或删除这些单引号？

补充说明：很抱歉，我没有包括@name实际上是通过连接从另一个数据库表填充的部分，因此在创建字符串之前设置@name的值可能对我来说很困难。

- Elaine K

---

为什么你不能在填充@name的时候就做那件事呢？ - HLGEM

Answer 1

为什么需要这样做呢？你应该将强参数传递给sp_executesql，而不是将所有参数混合成一个字符串并使用EXEC()。在此处了解更多信息。

DECLARE @sql NVARCHAR(MAX), @name NVARCHAR(32);

SET @name = 'Pete''s Corner';

SET @sql = 'INSERT INTO ' + @tempTable1 +
  ' SELECT 0 as Type1, 0 as Type2, @name as CompanyName ' + 
  ' FROM #tempTable2 tt2';

EXEC sp_executesql @sql, N'@name NVARCHAR(32)', @name;

我猜测@name参数实际上是在其他地方填充的，如果使用适当的参数化，你不必处理转义'。

现在我不太确定@tempTable1应该表示什么，或者你是否可以从这个作用域访问#tempTable2，但每当你发现自己需要''''或''''''（或两者都需要）时，你应该问自己是否有更好的方法。

- Aaron Bertrand

Answer 2

我认为这应该可以解决：

 DECLARE @sql varchar(max)
SET @sql = ' INSERT INTO ' + @tempTable1 + 
       ' SELECT 0 as Type1, 0 as Type2, ' + ''''+
         replace( @name ,'''','''''')+''''+' as CompanyName  
       FROM #tempTable2 tt2'

- jymbo

Answer 3

你可以使用sp_executesql系统存储过程。sp_executesql允许你使用@name参数调用动态SQL，而不是将它嵌入到SQL语句中。

DECLARE @sql nvarchar(max),
        @name varchar(50)
SET @name = 'qwe'''           
SET @sql = 'INSERT INTO ' + @tempTable1 +
           ' SELECT 0 as Type1, 0 as Type2, ' + 
           '@name as CompanyName ' + 
           'FROM #tempTable2 tt2'
--PRINT @sql
EXEC sp_executesql @sql, N'@name varchar(50)', @name

- Aleksandr Fedorenko