Richard Swinbank shows off one of the more common uses of dynamic SQL:
Now you come to ingest your second table – and you have to do this all again!
It’s a lot of code, and worse, it’s all basically the same – only the names and types are any different. You can easily make a mistake, and the boredom of repetition only makes that more likely. Later on, when you decide you want to add something to the process – a logging statement at the end of the stored proc, for example – you’ll have to add it separately to every stored procedure you’ve created.
The solution? Generate your code automatically, from metadata, using dynamic SQL!
Read on for what you’d need to pull this off.