Skip to main content
Software Engineering

You are not logged in. Your edit will be placed in a queue until it is peer reviewed.

We welcome edits that make the post easier to understand and more valuable for readers. Because community members review edits, please try to make the post substantially better than how you found it, for example, by fixing grammar or adding additional resources and hyperlinks.

Required fields*

7
  • 17
    5. Parametrized queries are more readable and maintainable. WHERE Foo = @Bar is much more meaningful than Foo = {0}. String format with dozen parameters is not fun. As a bonus, string interpolation combined with nameof() will keep query up to date during automatic refactor. 6. Parametrized queries are not bound to peculiarities of String.Format of language/framework/runtime. Not only query will work elsewhere, you won't have to deal with parameter order. 7. Parametrized query won't break when using float/date on system with different locale settings than database. Commented Mar 7, 2023 at 21:24
  • 4
    @PTwr: 8. Parameterized queries' execution plans are better cached by databases. Commented Mar 8, 2023 at 8:42
  • 2
    @PTwr: Your 5 and 6 are possibly a little dubious: some database libraries (*cough* Perl DBI *cough*) have parameterised syntax like WHERE Foo = ?; conversely some string format libraries (eg Python) allow WHERE Foo = {foo}. (Not that this should discourage anyone from using parameterised queries - the advantages still massively outweigh the disadvantages!) Commented Mar 8, 2023 at 10:27
  • 2
    @Matthieu M. Query plan caching depends on the RDBMS, and it might even have ill side effects. I gave a hunch that it was Oracle which will happily ignore the parameters (and the corresponding statistics) when it hits a parameterized query where the plan is already cached. A WHERE clause like "status = :status" might give a full table scan with :status = 'closed', while :status = 'open' uses an index (table has a few dozen rows with status 'open', billions with status 'closed'). Additional fun, like when a SORT BY is present, might use a suboptimal index. Using the same plan sucks. Commented Mar 8, 2023 at 12:17
  • 1
    @Klaws: There are indeed corner cases, in general though it's beneficial. Commented Mar 8, 2023 at 12:32