Comment by ameliaquining

5 months ago

Only for parameterization over scalar values. If you want to do any kind of composition more sophisticated than that, you're either stitching together strings or using some kind of more heavyweight abstraction like an ORM.

14 comments

ameliaquining

Reply
monkeyelite  5 months ago

That’s because the composition is supposed to be inside sql. Views, functions, etc.

This is another reason why the ORM is a leaky abstraction - it hides all the best features from you.

  • ameliaquining  5 months ago

    I suspect the biggest reason those aren't more popular is that they usually have to be stored as state in the database, which isn't what you want when developing an application. You want all of your query logic to be versioned with your application code.

    • branko_d  5 months ago

      > You want all of your query logic to be versioned with your application code.

      SQL can be stored in version control just as well as any other code. This can include application-level queries as well as the DDL SQL which defines the actual structure of your database.

      It's sad that tooling for this kind of workflow doesn't seem to be particularly good across the board, Visual Studio being somewhat of an exception.

      3 replies →

    • monkeyelite  5 months ago

      In most organizations a database is broader than any individual application - both in lifecycle and scope. So it makes sense that this state exists in a different way.

      I suspect it’s because people never learned to use them, but they did learn to use the ORM.

      3 replies →

branko_d  5 months ago

> Only for parameterization over scalar values.

ADO.NET has full support for table-valued parameters.

  • ameliaquining  5 months ago

    That's part of .NET Framework and therefore legacy, right? Do the database libraries from recent versions of .NET do this?

    In any case, it's just one framework; previous comment said "all major languages". And it's useful to be able to abstract and compose over expressions and predicates and such, not just data values, which this still doesn't help with.

    • branko_d  5 months ago

      > That's part of .NET Framework and therefore legacy, right? Do the database libraries from recent versions of .NET do this?

      ADO.NET is available both in the legacy Windows-only .NET Framework and in the new cross-platform .NET (previously known as .NET Core).

      > In any case, it's just one framework; previous comment said "all major languages".

      Well, you are not implementing a piece of code in "all major languages" - you can pick the one that fits the problem best.

      > And it's useful to be able to abstract and compose over expressions and predicates and such, not just data values, which this still doesn't help with.

      You can do that via LINQ - there is even special query-like syntax built right into C# for that that looks like this:

          var companyNameQuery =
        from cust in nw.Customers
        where cust.City == "London"
        select cust.CompanyName;

      This does NOT load the entire table in memory just to filter on City. It actually transpiles to SQL which does the filtering on the server.

      But anything non-trivial is much better done in SQL proper, IMO. Most of the time, at least for OLTP, you'll be using static SQL - that is you will not need to change the text of the SQL query, just parameters. But dynamic SQL is a thing and can be very useful on occasion - which is string concatenation with all the problems that might bring.