[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Stored procedures in n-tier architecture question

Posted on 2006-04-08
Medium Priority
Last Modified: 2013-11-12
What should stored procedures be used for in 3-tier architecture and why?
What should they not be used for and why?
Question by:rj2
LVL 14

Accepted Solution

MilanKM earned 400 total points
ID: 16410731
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.


Assisted Solution

sanjaykashyap19 earned 200 total points
ID: 16413812
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.

LVL 12

Assisted Solution

AGBrown earned 200 total points
ID: 16432285
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.


Assisted Solution

morisce earned 200 total points
ID: 16502436
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.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wonder how to "do" object oriented programming (OOP)?
Software development teams often use in-memory caches to improve performance. They want to speed up access to, or reduce load on, a backing store (database, file system, etc.) by keeping some or all of the data in memory.   You should implement a…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question