title: SQL Dynamic Query String Breaks When Variable Contains Single Quote
body: <p>I have a SQL query string that is like this: </p>
  
  <pre><code>DECLARE @sql varchar(max)
  SET @sql = ' INSERT INTO ' + @tempTable1 + 
             ' SELECT 0 as Type1, 0 as Type2, ' + 
               '''' + @name + ''' as CompanyName ' + 
             ' FROM #tempTable2 tt2'
  </code></pre>
  
  <p>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.</p>
  
  <p>How can I replace or remove these single quotes? </p>
  
  <p>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. </p>