I've worked on a few apps that relied heavily on SQL stored procedures as the data access layer. In a database like SQL Server or Sybase, a stored procedure is like a function with a body of SQL that is stored on the server, receives typed parameters and can return more than one result set, each result set having a different shape (different columns). So, a call to get an entire graph of data would only require one request and one response, saving many round trips. You could write a stored procedure that selects a customer, their recent orders, the products on each of the orders and so forth. You can also insert/update into multiple tables in one trip and you can wrap any parts of your SQL in transactions.
The SQL variant used by SQL Server and Sybase, called Transact SQL, also has elements of procedural programming such as variables, flow control with IF/THEN/ELSE/switches/etc, looping, calling other SQL procedures or user defined functions, exception handling and so on.
These features have been part of SQL Server since the 90s. It's extremely powerful and if used well, can be much more elegant and flexible than an ORM in my opinion. It's a shame that PostgreSQL doesn't support stored procedures quite as well or have an SQL variant that's as well crafted as Transact SQL because I'd love to be able to write apps in that style again. Somewhat recently I believe PG got something like stored procedures that can return multiple heterogeneous result sets and although I can't remember the specifics, I don't think it's quite as robust as what you can do with TSQL and it lacked client/driver side support maybe... (Also PG has some support for running TSQL itself, but not with all the same features.)
The SQL variant used by SQL Server and Sybase, called Transact SQL, also has elements of procedural programming such as variables, flow control with IF/THEN/ELSE/switches/etc, looping, calling other SQL procedures or user defined functions, exception handling and so on.
These features have been part of SQL Server since the 90s. It's extremely powerful and if used well, can be much more elegant and flexible than an ORM in my opinion. It's a shame that PostgreSQL doesn't support stored procedures quite as well or have an SQL variant that's as well crafted as Transact SQL because I'd love to be able to write apps in that style again. Somewhat recently I believe PG got something like stored procedures that can return multiple heterogeneous result sets and although I can't remember the specifics, I don't think it's quite as robust as what you can do with TSQL and it lacked client/driver side support maybe... (Also PG has some support for running TSQL itself, but not with all the same features.)
Here's an example of how you might call a stored procedure in C#, using a library written by Marc Gravell of StackOverflow - https://stackoverflow.com/questions/5962117/is-there-a-way-t...