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

I have a SQL query string that is like this:

我有一个SQL查询字符串，如下所示：

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

The query runs fine except for two names that happen to contain a single quote (ex: Pete's Corner). When either one of these names becomes part of the query it breaks the query string. I thought the easiest thing to do would be to replace the single quote like this replace(@name,'''','') but it doesn't work because I'm already in a string and so its affecting the rest of the statement. Altering the table itself is not an option unfortunately.

查询运行正常，除了恰好包含单引号的两个名称（例如：Pete's Corner）。当这些名称中的任何一个成为查询的一部分时，它会破坏查询字符串。我认为最简单的方法是替换单引号，如替换（@name，''''，''），但它不起作用，因为我已经在一个字符串中，所以它影响其余的该声明。不幸的是，改变表本身并不是一种选择。

How can I replace or remove these single quotes?

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

Addition: I apologize, I did not include the part where @name is actually being populated from another database table by a join so setting the value of @name before the string is created I think would be difficult for me.

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

3 个解决方案

#1

I think this should do it:

我认为应该这样做：

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

#2

Why do you need to do this at all? You should be passing strong parameters to sp_executesql instead of munging all of your parameters into a single string and using EXEC(). More info on that here.

为什么你需要这样做？您应该将强参数传递给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;

I presume the @name parameter actually gets populated from elsewhere, and if using proper parameterization you shouldn't have to deal with escaping the '.

我假设@name参数实际上是从其他地方填充的，如果使用正确的参数化，你不应该处理转义'。

Now I'm not quite sure what @tempTable1 is supposed to represent, or if you can access #tempTable2 from this scope, but whenever you find yourself running a replace that requires '''' or '''''' (or both), you should ask yourself if maybe there's a better way.

现在我不太确定@ tempTable1应该代表什么，或者你是否可以从这个范围访问＃tempTable2，但每当你发现自己运行需要''''或'''''的替换时（或两者都有）），你应该问自己是否有更好的方法。

#3

You can use sp_executesql system procedure. sp_executesql will allow you to call dynamic SQL with @name parameter instead of embedding it into the SQL.

您可以使用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

#1