This is an old pattern (like early 2000s MS SQL app development) and works wonderfully for the majority of applications.
It does become complex and awkward in two case, 1) variable option selection and 2) at a certain "scale" / quantity of inserts/deletes.
Variable option selection
Take for example a search interface of dependent items and item options, like cars. The query for car options for different car models generally returns a variable list of options. Further filtering results based on the variable list of options, when the list of options is all in one table, is most efficient with an "in clause". Trying to represent the "in clause" using functions/stored procedures is a little awkward as there is list construction and deconstruction involved, and sql is not great at list/string parsing.
Number of inserts/deletes
At some point, a successful app will reach a point where it's more efficient to batch insert & delete -- a couple hundred rows. This goes back again to using "in clauses' and the stored procedure approach makes things a bit awkward, as then the store procedure will need to argument parse and check.
Not saying what's done here is bad - it's actually really good for many, many reasons. I'd actually recommend the approach and avoid what I've described above unless it makes the desired functionality hard and complex.
It does become complex and awkward in two case, 1) variable option selection and 2) at a certain "scale" / quantity of inserts/deletes.
Variable option selection
Take for example a search interface of dependent items and item options, like cars. The query for car options for different car models generally returns a variable list of options. Further filtering results based on the variable list of options, when the list of options is all in one table, is most efficient with an "in clause". Trying to represent the "in clause" using functions/stored procedures is a little awkward as there is list construction and deconstruction involved, and sql is not great at list/string parsing.
Number of inserts/deletes
At some point, a successful app will reach a point where it's more efficient to batch insert & delete -- a couple hundred rows. This goes back again to using "in clauses' and the stored procedure approach makes things a bit awkward, as then the store procedure will need to argument parse and check.
Not saying what's done here is bad - it's actually really good for many, many reasons. I'd actually recommend the approach and avoid what I've described above unless it makes the desired functionality hard and complex.