There are many ways that you can structure your T-SQL code to solve complex problems. Throughout my career, I've learned different ways, and also some patterns to avoid. However, those change over time. For example, there was a time when heavily loading tempdb caused issues in the v6 era. As a result, I tried to avoid using them. These days, I'd consider them a good option to think about.

One of the techniques that I see people trying often is Dynamic SQL. This is where you don't write the code directly, but you write code that will put together a string of code to execute, and then you call this with EXECUTE() or some other mechanism to get your results.

There are places where you might find Dynamic SQL useful. I saw an article from Erik Darling recently that gives you some situations where Dynamic SQL can help. These are often situations where performance is worse with a bunch of conditional code that is jumbled together. Separating out code into different procs or functions can help here, but that becomes a maintenance headache in many ways.

Dynamic SQL isn't something to just try without thinking carefully about the ramifications. SQL Injection is a real concern, though you can mitigate the risk with some careful coding. However, you need to be careful how you structure code, and don't just concatenate strings together and execute them. Read up on good practices from others, and ensure you test your code thoroughly. In fact, this is one place where automated testing can become important, as you learn of different injection techniques, you can add new tests to ensure your code isn't vulnerable.

As you work with T-SQL, it's important to continue your education, learn how the platform and language work, how performance changes between versions, and what options are to be adopted or dropped. Becoming a better T-SQL developer takes work and practice, and it's an area in which we should all be constant students.