SQL Dynamic Query String Breaks When Variable Contains Single Quote

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.

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'

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.

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 @sqlEXEC sp_executesql @sql, N'@name varchar(50)', @name