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