Stored procedures seem like a win but the big problem is that while I could write the rest of the software in a very nice modern language like Rust, or more practically in C# since my team all know C# if I write a stored procedure it will be in Transact-SQL because that's the only choice.

T-SQL was not a good programming language last century when it was vaguely current, and so no I do not want to write any significant amount of code in T-SQL. For my sins I maintain a piece of software with huge T-SQL procedures (multi-page elaborations by somebody who really, really like this stuff) and they're a nightmare. The tooling doesn't really believe in version control, the diagnostics when you make a mistake are either non-existent or C++ style useless spew.

We hire a lot of very junior developers. People who still need to be told not to comment out code in release, that variable numbers are for humans to read not machines, that sort of thing. We're not quite hiring physicists to write software (I have done that at a startup) but it's close. However, none of the poor "My first program" code I see in a merge request by a new hire is anywhere close to as unreadable as the T-SQL we already own and maintain.

▲

Yokohiii 5 days ago | parent | next [-]

I've only once tried to use stored procedures in mysql and it was almost impossible to debug back then. Very painful. Average devs already have issues being smart with their databases and stored procedures would add to that.

Stored procedures also add another risk. You have to keep them in sync with code, making releases more error prone. So you have to add extra layers of complexity to manage versioning.

I can see the advantage of extreme performance/efficiency gains, but it should be really big to be justified.

▲

CuriouslyC 5 days ago | parent | next [-]

I'm a big postgres guy and in theory I love stored procedures (so many language options!) but you're 100% right that the downsides in terms of DX make them pretty much the last thing I reach for unless they're a big performance/simplicity win and I expect them to be pretty static over time.

▲

loglog 5 days ago | parent | prev [-]

> Stored procedures also add another risk. You have to keep them in sync with code, making releases more error prone.

This one is easily solved: never change a stored procedure. Every version should get a new name.

	▲	Yokohiii 5 days ago \| parent [-]
		That's what I meant when I've mentioned versioning.

▲

doitLP 5 days ago | parent | prev [-]

I worked at a place with just such a system. Half the application code was baked into sprocs, no version control and hidden knock on effects everywhere.

There was _one guy_ who maintained it and understood how it worked. He was very smart but central to the company’s operations. So having messy stuff makes it brittle/hard to change in more ways than one and