.net - Use of StoredProcs in an application

Question

we're in the process of designing an enterprise application & our technical architects suggest that "lets have no sql queries in the code. even if its a simple select call to the database a stored procedure should be written for it in the database & the code should call it."

my issue with this is that it seems like a dumb idea to have only stored procedures & no queries what so ever in the whole code! our guys are convinced on this idea but i have my doubts about it... what do you guys have to say?

if its a good idea, i'd love it if someone could tell me reasons supporting it.

and if its a bad idea, please tell me what argument can present to them to convince them otherwise.

appreciate your help.

thx -ksm

Answer 1

Here's open reason in favour of stored procedures: Why use Stored Procedures?

I'll also add: coded correctly, a stored procedure can be treated like a method: it has return type (dataset(s)), parameters etc

Saying that, I would be inclined nowadays:

• to force writes via stored procedures
• allow queries onto tables directly via Linq or such
• ensure I use stored procedures for complex aggregates

There is no "yes" or "no" answer