title: Single Quote Handling in Dynamic SQL Stored Procedure
body: <p>I'm using a query like this:</p>
  
  <pre><code>ALTER procedure [dbo].[procedure]
  @Search nvarchar(100) = ''
  declare @LargeComplexQuery nvarchar(max) =
  'select * from People where Name like ''%' + @Search + '% order by Name'
  exec sys.sp_executesql @LargeComplexQuery
  </code></pre>
  
  <p><code>@Search</code> is populated by a program.
  If the program returns a string containing a single quote the stored procedure errors, how can I handle this?</p>
  
  <p>If possible, I'd like this to be handled by the stored procedure, rather than the program passing in the string.</p>