3 ms·
Our good old CRUD app does that. We dynamically generate a WHERE clause based on the column(s) the user wants to search for, set the parameter value(s) based on
by magicalhippo 4y ago
Our good old CRUD app does that. We dynamically generate a WHERE clause based on the column(s) the user wants to search for, set the parameter value(s) based on the search values and rerun the query.
This approach allows us to have a one-liner to enable searching in a grid, and scales well to grids with millions of rows and 50+ columns.
Of course the user might issue a search on a single non-indexed column, but this hasn't been an issue in practice. Either it happens very seldom, or they've already filtered on something indexed.
We also have use dynamic SQL for cases where we needed to select from different tables or views depending on some run-time condition, or similar. No direct user input there though so don't really need the safety that parameters bring.
- magicalhippo 4y agoReading further I see the article goes into dynamic tables/views, and how it's normally a code smell. I agree it's not something you should do lightly, and you need to consider the cost vs the benefits. I mean clearly we could just maintain multiple near-identical static queries and just pick the right one, rather than have a single dynamic query. However, then you're stuck having to keep them in sync and you will eventually screw that up. In our case, we'll resort to that if the alternatives are too painful, which so far has just been a few places amongst our several hundred queries.