Back to Papers and Articles
Choice Between Stored Procedures, Functions, Views, Triggers, Inline SQL (updated)
Copyright 2005 Paragon Corporation   ( November 30, 2005)

Business Logic: To Store or not to Store that is the Question?

A lot of people in the database and programming professions are vehemently in favor or opposed to the use of stored procedures and the like in databases. They will argue that all access to the database should go thru stored procedures because it is more secure and shields applications from changing logic. The other side will vehemently argue that you should avoid this because not all databases support this and the way each supports it is different so your code is less portable.

I would start by saying those individuals who are vehemently on one side or the other should open their minds a little. One approach is not strictly the best solution for all business requirements. In the next couple of sections we'll outline what are the important questions to consider when choosing one approach over another. Often you will find that even in a single application a mixture of approaches works best.

Best Practices vs. Best approach to achieve the goal

Before dismissing one approach over another, it is important to map out what you are trying to achieve and then determine how using one approach aligns with your objectives.

You will often hear the term Best Practices used in Application Architecture. The danger of such a term is that it gives one a sense that there is no need to question a practice because it is always best. Just follow Best Practices outlined by an authority in the subject matter and things will magically fall into place. Rather than just focus on Best Practices, I would like to propose that one should think about what they are trying to achieve and why one approach is better than another to get there.

What to consider?

In this section, we will talk about considerations on a very elemental functional level rather than a more macro application level. We think it is important to consider each functional task of an application separately rather than thinking of an application as a whole. Example you may have one part of an application that needs to talk to a mainframe database or run scheduled tasks, and given that it doesn’t make sense to drive your whole application structure based on the need of this single functionality.

  • Does this function need to work in various kinds of databases?
  • Is this function used in multiple parts of an application or applications?
  • Does this function require many arguments and return one set of values in form of single table or scalar value?
  • Does this function require few arguments passed to the database, but require lots of data from the database to arrive at results?
  • Is the function data intensive or processor intensive? E.g. is it an encryption function or one to get results of a complex query - is it more SQL intensive or more procedural intensive
  • Are the parameters passed into the function consistently the same or can they vary? E.g. a complex search form will not always need to pass the same parameters, but a simple one will just have a single search field and always pass just that.
  • Does the function require page long batches of SQL statements or is it a one line SQL statement?
  • Are the fields of the result set always the same or do they need to vary. For example do you always find yourself joining the same set of tables varying slightly by the need for different fields or subsets of data?

Database Objects

I always find it amusing that when people talk about database logic they are very focused on stored procedures and its almost like nothing else exists. Makes me wonder if these people have ever worked with modern databases. Stored procedures are one of the oldest methods of encapsulating database logic, but they are not the only method available. Many relational databases nowadays have views, constraints, referential integrity with cascading update, delete, stored functions, triggers and the like. These are extremely powerful tools when used appropriately.

In the next couple of sections we’ll cover stored procedures and these other kinds of database objects and detail the strengths and weaknesses of each for encapsulating logic. We will give a rating of 0-5 for each feature 0 meaning the feature is non-existent, 5 meaning this object is one of the best suited objects for implementing this kind of task.

Stored Procedures

Stored procedures are one of numerous mechanisms of encapsulating database logic in the database. They are similar to regular programming language procedures in that they take arguments, do something, and sometimes return results and sometimes even change the values of the arguments they take when arguments are declared as output parameters. You will find that they are very similar to stored functions in that they can return data; however stored procedures can not be used in queries. Since stored procedures have the mechanism of taking arguments declared as OUTPUT they can in theory return more than one output.

FeatureRating
Works in various kinds of databases 3 (many  databases such as DB II, Oracle, SQL Server, MySQL 5, PostGreSQL, FireBird support them).  There are also a lot that don’t e.g. MySQL < 5.0, MS Access (although parameterized queries serve a similar role)
Can be called by multiple applications and interfaces 4 (generally they can be called, but the use of OUTPUT arguments  is not always usable)
Can take an undefined number of arguments 2 (note most databases allow to define optional arguments, but this can become very unwieldy to maintain if there are a lot  because you end up duplicating logic even within the stored procedure so is generally avoided)
Reusability within the database 3 (you can not reuse them in views, rarely in stored functions and other stored procedures unless the stored procedure using it does not require a return value or result query).  This varies slightly from DBMS to DBMS.
Can be used to change data in a table without giving rights to a user to change table directly 4  In general true for most DBMSs that support them.
Can return varying number of fields given different arguments. 3 –again in theory it can, but very hard to maintain since you would often be duplicating logic to say return one field in one situation and other set of fields in another situation or update a field when the field is passed in as an argument. Note that in many databases such as for example SQL Server and Oracle, one can return multiple result sets with a stored procedure, but the receiving end needs to be able to do a next result set call and know the sequence in which the result sets are being sent.
Long stretches of SQL easy to read and maintain 5 (one of the great strengths of stored procedures is that you can have long transactions of sql statements and conditional loops which can be all committed at once or rolled back as a unit. This also saves on network traffic.

Stored Functions

Stored Functions are very similar to stored procedures except in 3 major ways.

  1. Unlike stored procedures, they can be used in views, stored procedures, and other stored functions.
  2. In many databases they are prohibited from changing data or have ddl/dml limitations.  Note for databases such as PostGreSQL this is not true since the line between a stored function and a stored procedure is very greyed
  3. They generally can not take output arguments (placeholders) that are then passed back out with changed values.
FeatureRating
Works in various kinds of databases 3 (many  databases such as DB II, Oracle, SQL Server support them, MySQL 5, PostGreSQL).  There are also a lot that don’t e.g. MySQL < 5.0, MS Access
Can be called by multiple applications and interfaces 4 (generally they can be called, but the use of OUTPUT arguments  is not always usable)
Can take an undefined number of arguments 2 (note most databases allow to define optional arguments, but this can become very unwieldy to maintain if there are a lot  because you end up duplicating logic even within the stored function so is generally avoided)
Reusability within the database 5 (you can reuse them in views, in other stored functions and stored procedures).  This varies slightly from DBMS to DBMS.
Can be used to change data in a table without giving rights to a user to change table directly 3  Many databases do not allow changing of data in stored functions except temp table data, but those that do in general support this.
Can return varying number of fields given different arguments. 4 –For databases such as SQL Server, PostgreSQL, DB 2, Oracle that allow return tables and sets, you can selectively pick fields you want from within a query. So although the function always outputs the same number of fields, you can selectively use only some similar to what you can do with views. This is not true for scalar functions (MySQL 5.1- only supports scalar functions).
Long stretches of SQL easy to read 5 - yes - you can do fairly intensive multi-line processing which in the end returns one value or table to the user.

Views

Views are one of the greatest things invented since sliced bread. The main beauty of a view is that it can be used like a table in most situations, but unlike a table, it can encapsulate very complex calculations and commonly used joins. It can also use pretty much any object in the db except for stored procedures. Views are most useful when you always need to join the same set of tables say an Order with an Order Detail to get summary calculation fields etc.

FeatureRating
Works in various kinds of databases 4 (many  databases such as DB II, Oracle, SQL Server support them, MySQL 5, PostGreSQL, SQLite, MSAccess (calls it a query)).  There are also some that don’t e.g. MySQL < 5.0
Can be called by multiple applications and interfaces 5 (generally they can be called anywhere where you can call a table which is pretty much everywhere)
Can take an undefined number of arguments 5 (you can select subsets of columns and rows from a view just like you can from a table)
Reusability within the database 5 (you can reuse them in other views, in stored functions and stored procedures).
Can be used to change data in a table without giving rights to a user to change table directly. 3  In many databases Views are read-only and complex views are rarely updateable. Note that for example some databases such as Oracle,PostgreSQL, MS SQL Server , SQLite will allow you to update even a complex view by using an instead of trigger or rule against the view. MySQL 5, MS SQL Server and some others automatically make one table views updateable. MS Access has a fairly sophisticated update algorithm for queries in that it automatically makes one table and even multi-table queries updateable and allows deletes if you define primary keys appropriately. Also depending on the field a column comes from it will update that and also automatically create child records in child tables if you try to update a child column when there is no record.
Can return varying number of fields given different arguments. 4 – (you can select subsets of columns and rows from a view just like you can from a table). However you can't change the underlying structure e.g. return records from a different set of tables like you can with a stored procedure or function.
Long stretches of SQL easy to read. 3 (A view can often be defined with an administrative designer or using a color coded sql editor so is fairly easy to read) - in general though it is limited to only one select statement or unioned select statements.

Triggers And Rules

Triggers are objects generally tied to a table or view that run code based on certain events such as inserting data, before inserting data, updating/deleting data and before these events happen.

Triggers can be very great things and very dangerous things. Dangerous in the sense that they are tricky to debug, but powerful because no update to a table with a trigger can easily escape the trigger.

They are useful for making sure certain events always happen when data is inserted or updated - e.g. set complex default values of fields, inserting logging records into other tables.

Triggers are especially useful for one particular situation and that is for implementing instead of logic. For example as we said earlier, many views involving more than one table are not updateable. However in DBMS such as PostgreSQL, you can define a rule on a view that occurs when someone tries to update or insert into the view and will occur instead of the insert. The rule can be fairly complex and can layout how the tables should be updated in such a situation. MS SQL Server and SQLite let you do something similar with INSTEAD OF triggers. Note the term Rule is a little confusing in DBMS because they mean quite different things. In Microsoft SQL Server for example a Rule is an obsolete construct that was used to define constraints on tables. In PostgreSQL a Rule is very similar to a trigger except that it does not get triggered per row event and is defined without need of a handling function.
FeatureRating
Works in various kinds of databases 2 (many  databases such as DB II, Oracle, SQL Server support them, MySQL 5, PostGreSQL,).  There are lots that don't e.g. MySQL < 5.0, MySQL 5 limited, MS Access
Can be called by multiple applications and interfaces 5 (it just happens behind the scenes. No application can escape them)
Can take an undefined number of arguments 0 ( strictly for updating data and against a table or view )
Reusability within the database 0 - No
Can be used to change data in a table without giving rights to a user to change table directly. 4  In general yes for databases that support them
Can return varying number of fields given different arguments. 0 –Triggers are strictly for updating data
Long stretches of SQL easy to read. A trigger can often be defined with an administrative designer or using a color coded sql editor so is fairly easy to read 5

Foreign Key Constraints, Primary Key Constraints, Referential Integrity, Cascade Update/Delete

No true database should be without Constraints, Referential Integrity, and Cascade Update/Delete. You can define them with SQL DDL statements or using a relational designer. The use of these tools are limited but the purpose they serve can not be easily replicated with other database objects. These tools serve 2 main purposes.

  • Provide a declarative model of the database and how the data is related to each other. A self-documenting tool.
  • Insure that you don't mistakenly do something stupid in your coding to violate your model. If your model is sound, errors in your code signal a flaw in your programming logic. If you get errors in your programming logic, then verify that your model is sound.

FeatureRating
Works in various kinds of databases 3 (many  databases such as DB II, Oracle, SQL Server support them, MySQL 4+ (4 is limited, 3 very limited and varies depending on storage engine (InnoDB vs MyISAM)), PostGreSQL, MS Access).  There are lots that don't e.g. MySQL < 5.0
Can be called by multiple applications and interfaces 5 (it just happens behind the scenes. No application can escape them)
Can take an undefined number of arguments 0 (they take no arguments )
Reusability within the database 5 - Yes - no matter where your updates, inserts occur, they can not escape the iron-fist of the Key Constraints and Cascade Update/Delete rules next to disabling them.
Can be used to change data in a table without giving rights to a user to change table directly. 4  Really only applies to cascade update/delete rules Yes - cascade update/delete rules are a special kind of trigger so to speak that kick in when a potential referential integrity constraint can be violated. Example if you update a key field, a cascade update on the foreign keys will force an update on the foreign key field to correct the situation so you don't end up with orphan data.
Can return varying number of fields given different arguments. 0 –not relevant
Long stretches of SQL easy to read. A trigger can often be defined with an administrative designer or using a color coded sql editor so is fairly easy to read 0 - not relevant

Dynamically Generated SQL

In this section we discuss the pros and cons of using dynamically generated sql vs. only using stored procedures, view, etc.

FeatureRating
Works in various kinds of databases 4 (guaranteed to work for any database, although the syntax may vary from database.)
Can be called by multiple applications and interfaces 3 (Will only work in the application where the dynamic sql is defined or if the application is wrapped as a shared library. The use of shared library is often limited as to where you can use it. However your logic can be applied to multiple databases without needing to alter the db by adding stored procs etc. if you are always using the same app to access these databases.)
Can take an undefined number of arguments 5 (you can select subsets of columns and rows from tables, change tables you are reading from etc.)
Reusability within the database 2 (non-existent except in some very rare situations and databases that allow you to load shared libraries).
Can be used to change data in a table without giving rights to a user to change table directly. 4  You can have the application control the security so in that sense the application only needs to have rights
Can return varying number of fields given different arguments. 5 – (you can select subsets of columns and rows, do complex joins ). You can change the underlying structure e.g. return records from a different set of tables.
Long stretches of SQL easy to read. 2 - SQL often sits interspersed with other programming logic which makes it somewhat difficult to read depending on the application language and also difficult to write complex transactional sql.

What about Security?

In the above criteria, we didn't put Security in as a feature/benefit even though many people will argue that dynamic SQL and the like are not secure. This actually varies depending on how you implement Dynamic SQL, e.g. if you are using an O/R Mapper or built-in dataadapters (supported in .NET),prepared statements, or are very careful to sanitize inputs you are using in your sql then your Dynamic SQL will probably be just as secure or sometimes more so than using stored procedures since some people are of the mindset that stored procedures are a magic bullet for protecting against SQL Injection and other attacks (such as script injection) regardless of how they are implemented and called and thus neglect other necessary precautions.

Articles of Interest

Database Abstraction with Updateable ViewsHere is an example in PostgreSQL that uses a view to do things people normally do in stored procedures.
Stored Procedures in PostgreSQLDiscussion on how PostgreSQL similulates stored procedures via advanced stored function implementation.
Stored Procedures vs. ad-hoc SQL Article that summarizes some arguments for and against stored procedures
Stored Procedures are bad, m'kay? Frans Bouma's critical review of Ron Howard's comments and details why you should avoid stored procedures
Don't use stored procedures yet? Must be suffering from NIHS (Not Invented Here Syndrome) Ron Howard's comments on why you should only use stored procedures.
MySQL 5.0 New Features: Stored Procedures Article by Peter Gulutzan summarizing why you should utilize MySQL's 5.0 new Stored Procedure feature. The PDF goes more into detail about creating MySQL Stored procedures. He makes a good point about if you know you might be changing your front-end/middletare programming language but plan to stick with the same database you should compartmentalize your logic in stored procedures. Also the MySQL 5.0 supports the SQL 2003 ANSI standard so stored procs are fairly portable to other SQL 2003 ANSI compatible databases such as DB II.
Should I use a view, a stored procedure, or a user-defined function? Provides a Microsoft SQL Server specific summary of the pros and cons of using each with some very concise examples of stored procedures and UDFs. Even though the article is tailored to Microsoft SQL Server, the concepts and rationale provided apply to most databases that support these features.
Good and Evil in the Garden of Stored ProceduresSome useful observations by Jeremy D. Miller on the pros and cons of using stored procedures
Foreign Keys and Referential IntegrityMySQL pre 5.0 Gotchas
An Introduction to Trigger in DB2 for OS/390 - while this is DB II specific most of the concepts can be applied to most other databases that support triggers



Back to Papers and Articles