[cedricium]

I'm going to project my naivety here and ask how does one write raw SQL queries in code? Should said queries live in their own files and be referenced when needed or are the queries usually written where they are called?

Bonus points for open source examples.

[wayneftw]

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.)

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...

[AdamM12]

I've personally seen and done both. At one job (first actual dev job) it was a lot of complex business queries for analytics. I put these into their own python module as string "constants" and imported them (I would not do this again). At my current job we put them in .sql files and have a 3 line function that opens and reads them into a string. I sometimes write them inline. I've come to only one real conclusion though is that unless it's super trivial I put the SQL outside of where they are being invoked and name the .sql something that makes sense. It's just noise in my code and I want to see the logical steps.

[kristopolous]

Every orm I know about has a raw interface for doing this