0 calories syntactic sugar for your sp_executesql statements

Today I'll share a helper procedure, which doesn't add much to your code, but some syntactic sugar and readability.

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!