Dynamic SQL is a good choice for catch-all type queries, but you have to be careful how you execute the dynamic string.
For frequently executed queries, “EXEC sp_executesql @sql” is a good choice but at the risk of encountering parameter sniffing issues.
For queries that aren’t executed very often, “EXEC (@sql)” can be used, but you could end up with a bloated plan cache with lots of single-use execution plans.
Let’s compare the performance difference between “EXEC (@sql)” and “EXEC sp_executesql @sql” for a frequently executed query.