Use of StoredProcs in an application

Question

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

Answer 2

Avoiding SQL queries in code is good practice. Hoever, modern best practice for avoiding inline SQL does not involve writing stored procedures, but rather to using Object-Relational Mapping technology. This reduces the amount of code and importantly ensures that logic doesn't enter the data tier.

Answer 3

There's no right answer to this. Stored procedures will make you code look a little cleaner, they may even run faster on some SQL servers but they for sure are much harder to maintain, change and migrate (especially when you need to switch to another SQL vendor for any reason).

Answer 4

My opinion on having ALL queries executed via stored procedures

Point 1:

It was REALLY useful in the days when having a DAL layer was not very common because doing it via SP's provided the same confidence that your DB changes would never impact any over lying layers other than 1.

Nowadays, with DAL layers being the norm, you get the same separation using a DAL and i do not really see too much additional value to having SP's in this regard. (Having a DAL layer assumes that your SQL queries are only in your DAL and nowhere else)

Point 2 :

Also, in older databases (atleast SQL Server 7 and maybe even 2000), there was a performance benefit due to caching of SP execution plans where as executing direct SQL would be slower due to this.

Nowdays, most databases cache ad-hoc queries also and this benefit too is no longer a criteria.

Point 3 :

Also, in older systems, a lot of business logic used to be embedded in the database. Having business logic embedded in the database meant writing LONG and complicated procedures. So, since procedures were anyways present, it became useful to make it consistent by saying "lets make everything a procedure"

Nowdays, most applications refrain from putting business logic anywhere in the database. So it is very likely that other than SELECTS, the CRUD statements will usually be very simple and straightforward in most object oriented systems.

Summary : I personally think that forcing a SP for every query is old fashioned. There is no harm to doing it. But i am really hard pressed to think of any real benefit to doing it provided you have a DAL / Business layer and a DB engine good enough to cache your query plans for your queries

Answer 5

Stored procedures may seem like overkill in some instances but they are not.

Let's say you are trying to figure out if a stored procedure query will return the results you want. You can set default values for your input parameters and run the query directly from query analyzer and you do not have to use the application.

If you want to tweek a stored procedure query without interfering with the application. You can simply make a copy of the stored procedure and tweak and test the copy over and over until it's correct.

This assumes your stored procedure is only querying the data and not modifying the data.

Once you start using stored procedures, and get comfortable with them you will say to yourself, "Wow, I wish I had been using these a long time ago."

Stored procedures also help you prevent SQL Injection in web based applications. If your application is web based then do some reading on how to prevent SQL Injection.