Stored procedures in n-tier architecture question

What should stored procedures be used for in 3-tier architecture and why?
What should they not be used for and why?
LVL 10
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Stored procedures are a type of facility from the database provider. They have a limited application field and a proprietary nature. The operational execution of a stored procedure including what it is and how it is implemented differs from one database to another.

In many cases stored procedures allow an API to be defined for a database, rather than having a client application interact with the tables and other database objects directly. It embeds Business Logic in the database using stored procedures that eliminates the need to duplicate the same logic in each of the programs which accessed the data. So this simplifies the creation and maintenance of the programs  and can also helps to avoid the data corruption that can be introduced if one of those external systems fails to have the validation logic upgraded properly.

So, the fact is quite clear now what the stored procedures are exactly. Now it a typical 3-tier application stored procedures are being used in the application layer and the Data tables being the Data layer. What is important of 3 tier is to spit the application and presentation layer.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
3-tier architecture  is 1st tier  UI, 2nd tier business logic  and 3rd tier is database.

What should they not be used for and why?
Stored procedures are use for writing business logic. If we write stored procedures its mean we are writing business logic in Database Tier. Then this architecture is not fulfilling the all rule of 3-tier architecture.

What should stored procedures be used for in 3-tier architecture and why?
But some process needs too much data process. If we process that data in 2nd tier (business logic), that will go to need too much data transfer, it will increase network traffic and it effect performance. In this case we use Stored procedures.

There is another benefit to stored procedures aside from "business logic in the database" - they have large security gains over dynamic SQL in your code. With stored procedures you can govern exactly who can do what to your tables from outside the database. If you don't define stored procedures you frequently have to leave the database completely open for all users to interact with CRUD rights on all tables.

They can also reduce the number of calls between code and database, and simplify transaction control to ensure your code abides by the ACID rule by manipulating multiple tables in one go.

I would advise against embedding proprietary business validation logic in stored procedures, this should be kept in the business layer to keep an invalid communication from ever reaching the database. You might, however, include some granular data-integrity logic in the stored procedure, or even concurrency logic.

They should not be used as the application layer of your architecture :
* Stored procedures depend on the DBMS. What happens when you decide to manage more than one RDMS ? You will do reengineering. Costs wil rise lineary
* Stored procedures can implement business logic but not efficiently for complex logic (bad error handling, poor data structures, many restrictions, ...). You should use embedded components (java beans, C++, ...)

Sql Procedure should be considered as a part of the data layer in a n-tier architecture.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming Theory

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.