Don't API services do that?

How do they do that in the context of ORM vs sps :)?

Calling code is the front end... API services separate that calling code from the backend.

your backend is still storing data somewhere right?

Yes, but I'm not sure I get the benefit of decoupling the schema, I'm still coupling to the stored procedure interfaces, and still have to deal with the shape of input and results.

If schema needs to be changed in many cases sp interface will stay the same e.g. I can do changes/optimizations to schema without changing the calling code.

But, if it's a schema change where you have to update the SP, you still have to change code... it's that the code is in (PL/T)SQL vs in another language.. You still have to update code either way.