Shane O’Neill covers an important topic:
There are a myriad number of uses for Dynamic SQL – I’ve already read some of the published posts and I’m impressed with the range. (Yeah, I’m writing this late; I’m aware).
I’m aiming for something different. I want to talk about the things I believe Dynamic SQL should have. You can disagree with me if you’d like; I’d welcome it (as long as you can justify it) but here are my thoughts on writing Dynamic SQL.
Improving the readability of code is probably the most important important things we could do at the margin. There are certainly trade-offs here: some patterns of code can be significantly more efficient at the cost of a minor readability loss and, if you need that to reach some expected performance threshold, fine. But ceteris paribus, the more human-readable code is the better code.