T-SQL inserting a string into a text datatype column
<p>I have a T-SQL stored procedure where I need to insert the value of a parameter into a text field in a table. The value coming in via parameter is actually a query that I later execute as dynamic SQL. When I enter just a single line without any quotes or other special characters like dash (-), it works fine. But with a quote or special chars, it barfs, since the quotes and special chars throws it off.</p>

<p>My input parameter for the stored procedure is:</p>

<pre><code>@input_query text
</code></pre>

<p>My value coming is this:</p>

<pre><code>Select 
    t1.*, t2.name 
from 
    nyc..sellers t1 
right join 
    ark..buyers t2 on t1.id = t2.id
where 
    t1.date = 'period' 
    and t2.period between '2016-01-01' and '2016-12-31' 
    and t2.def = 'u'
</code></pre>

<p>When I try to execute the stored procedure like this:</p>

<pre><code>DECLARE @return_value int

EXEC @return_value = [dbo].[booksellers]
     @input_query = N'Select t1.*, t2.name from nyc..sellers t1 right join ark..buyers t2 on
  t1.id = t2.id where t1.date = 'period' and t2.period between '2016-01-01' and '2016-12-31' and t2.def = 'u''

SELECT  'Return Value' = @return_value
</code></pre>

<p>I get following error: </p>

<blockquote>
  <p>INCORRECT SYNTAX NEAR 'period'</p>
</blockquote>

<p>I realize it's because the quotes are not escaped and even if they are, the next error will be with the dates not being escaped and so on. But I can't mess with that input query - it needs to be inserted as is. Is there a way I can get it to work?</p>