Scott Coleman (5/15/2015)

---

If an OPENQUERY statement could make use of the @EndDate parameter, he wouldn't need dynamic SQL in the first place.

I have a lot of code that references our non-SQL accounting system, and the drivers require OPENQUERY statements instead of four-part names. So I run into this issue all the time. Some of the code I inherited builds dynamic SQL with so many concatenated expressions that it is hard to see what the statement is doing.

I prefer to use a template statement with replacement tags for all the parameters, then surround it with a REPLACE() function for each tag.

SET @cmd = REPLACE(REPLACE(REPLACE(

'SELECT * FROM OPENQUERY(Link, ''SELECT * FROM tbl

WHERE intcol=<intval> AND strcol="<strval>" AND dtcol > <dateval>'')',

'<intval>', @Int),

'<strval>', @STR),

'<dateval>', CONVERT(CHAR(10), @Date, 101));

This would be formatted for the system I deal with, which uses double-quote delimiters for string literals and accepts date literals with no delimiters but only in the format MM/DD/YYYY.

I find this to be a lot more readable, especially if some tags are used more than once in the template. It's much easier to verify that the basic statement has the right number of delimiters in all the right places. The replacement values can be expressions, and the REPLACE function will do an implicit CAST to VARCHAR of things like integers. I'm now dealing with another level of linked servers so I have nested OPENQUERYs, with plenty of doubled and quadrupled quotes. If I had to add parameters using "...''''' + CAST(@Int AS VARCHAR) + '''''..." I would go nuts counting the stupid quotes. And fighting with Intellisense when it decides to add extras.