Sunday, May 27, 2018

sql server - T-SQL inserting a string into a text datatype column

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.



My input parameter for the stored procedure is:



@input_query text



My value coming is this:



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'


When I try to execute the stored procedure like this:



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


I get following error:





INCORRECT SYNTAX NEAR 'period'




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?

No comments:

Post a Comment

plot explanation - Why did Peaches' mom hang on the tree? - Movies & TV

In the middle of the movie Ice Age: Continental Drift Peaches' mom asked Peaches to go to sleep. Then, she hung on the tree. This parti...