Stored procedures in n-tier architecture question

Posted on 2006-04-08
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

    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.

    LVL 5

    Assisted Solution

    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

    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.

    LVL 5

    Assisted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Pretext and Context In previous  (, I designed the data model of a basic Identity Management…
    Introduction Many of the most common information processing tasks require sorting data sets.  For example, you may want to find the largest or smallest value in a collection.  Or you may want to order the data set in numeric or alphabetical order. …
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now