SQL Dynamic Query String Breaks When Variable Contains Single Quote

Question

I have a SQL query string that is like this:

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.

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.

Answer 1

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.

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 '.

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.