Kurt W. Zimmerman (10/7/2013)
A simple way to debug dynamic SQL is to simply print it out. Then copy/paste the output into a New Query window and see where your errors are.
I'm not a big fan of dynamic SQL but others before me were. There are so many times I have debugged dynamic SQL in that fashion.
FYI, right now if I have to resort to dynamic SQL it is only in one-off scripts for a single purpose.
Anytime I write a SP that uses dynamic SQL I include a @debug parameter so that I can EXEC the SP with @debug=1 to print the SQL. Unfortunately, if it gets too long there are limitations on what can display in the Messages pane (4000 characters I think).
I do this as well. It has saved a lot of time debugging some of the more complex SPs. Great tip.
Kurt W. Zimmerman
New York, NY