July 30, 2013 at 4:44 pm
Hi All,
I have an application which automatically generates SQL - To connect my application to my SAAS database I am having to use a LINKED server.
The problem I have is with single quotes as the SQL used has to be enclosed in single quotes.
The string my application adds to the sql is: a.acctnum like 'ME4%' and e.entityid='100AAA'
I need a way of taking this where clause and added an addtional set of single quotes.
July 31, 2013 at 2:50 pm
Single quotes have to be escaped, which is done by immediately following the single quote you are using as the character in string with another single quote.
For example, if I have the following query:
SELECT id FROM Employee WHERE FirstName='John'
It would be quoted (in OPENQUERY, EXEC, etc.) as:
'SELECT id FROM Employee WHERE FirstName=''John'''
Escaping single quotes can be a bit confusing at first, but if you step through strings like the above a few times it becomes easier to understand.
Hope this helps!
July 31, 2013 at 3:34 pm
Running dynamically generated queries through OPENQUERY requires strict discipline in order to protect your mental health. Look at http://www.sommarskog.se/dynamic_sql.html#OPENQUERY for some tips.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 31, 2013 at 11:44 pm
Erland Sommarskog (7/31/2013)
Running dynamically generated queries through OPENQUERY requires strict discipline in order to protect your mental health. Look at http://www.sommarskog.se/dynamic_sql.html#OPENQUERY for some tips.
Excellent article Somm 🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply