If you actively use integrations with other systems through linked servers, you inevitable ran into a limitation of
openquery()
statement, as it can only accept static strings as queries to an external data source. A workaround is to build a string containing full statement, and then use insert ... execute sp_executesql ...
construct. Putting the whole conversation about efficiency and error handling a side, your code will become not much readable, as you need double twice single quotes - first to escape them in your MS SQL string assignment and second time as you building a parameter for openquery()
, which should be a string in its own term.As there's more than one kind of quotes available, I decided to add a simple procedure, that would save me from counting single quotes in my string and do this trick for me. So, here's the procedure code:
It properly escapes double quotes
"
with the right number of single quotes for embedding your string parameters, and does the same for grave accents `
as single quotes in the beginning and the end of the query string.And here's it's usage, you can compare readability of the statements with and without using it for yourself:
The code should be self-explanatory, but take a note of the second optional
@debug
parameter to sp_executequotedsql
. Once set to 1, it will print out resulting parameter for the standard sp_executesql
procedure, so you can inspect that quoting is done right. Enjoy!
No comments:
Post a Comment