3 ms·
I see Dynamic SQL being used the most for custom queries that require dynamic columns, or especially, dynamic ordering. Using sprocs won't help you there as you
by DaiPlusPlus 10y ago
I see Dynamic SQL being used the most for custom queries that require dynamic columns, or especially, dynamic ordering. Using sprocs won't help you there as you still need Dynamic SQL under the hood. I think the best solution is something like Linq-to-Entities with manually defined order-by logic for each query (e.g. an enum defining the sort-order possibilities) or reflection-based matching, neither of which is pretty.
- smnc 10y agoDynamic SQL is not the problem, you can safely construct queries on runtime in most RDBMS-s, the key thing is to let the DB treat user input as parameters. You can concat strings to create dynamic queries all you want, just don't blindly shove user input in there, construct the query dynamicaly with parameter placeholders and then bind the user-passed input as parameters. Of course, there's still the matter of those queries not nessarily being syntactically or semantically correct which will only bite you on runtime, but that's a different story. If you're doing dynamic SQL you either know what you're doing, or don't know that you don't know what you're doing. Although that probably goes for most of things in life.