Stored procedures – why use them?

Piotr Rodak has posted an interesting article on the use of instead of triggers. The discussion extended to one about applications having direct access to SQL base tables.

This reminded me of the intense debates on the pros/cons of using stored procedures. These debates often centre on things such as security, performance – very rarely do they mention what I consider to be the most important reasons for using sprocs – the ability to decouple database schema from external applications allowing a more maintainable data contract to be provided.

The problem

After a number of years of use a typical corporate database  will often have a number of external applications that use it eg:

  • Web apps
  • Reporting apps
  • ETL/SSIS components to import/export data
  • Linked server access

These applications often have direct access to base tables. This can make it is very difficult to react quickly to business changes. Even basic refactoring eg base table name changes – become very onerous. A change to a base table will often require changes to all external applications that access it – there is no flexibility in approach all will have to change at the same time.

Allowing direct access to base tables often means that the database schema effectively becomes ‘static’ – as change is seen as being too difficult. In my experience this can lead to data quality issues as, for example, base tables then get reused for purposes that they were not intended for.

How to get around this?

1. Enforce the principle of not giving direct access to base tables to external apps. Direct access should be limited to a small number of views. Changes to base tables can then be isolated to only those views that directly access them.

2. External access should be via sprocs – in some cases this can be extended to views. If a base table needs to be changed then existing data contracts/interfaces are more easily maintained via sprocs – for views ‘instead of triggers’ can be used. In this way external applications are decoupled from these changes.

What about ORMs?

By default ORMs often use direct access base tables. Some such as NHibernate can use sprocs  – but they lose some of ‘their flexibility’ – see Ayende Rahien’s article Should you use NHibernate with Stored Procedure? In the scenario where dev teams are using ORMs and do not want to use sprocs – then the use of views can be a reasonable compromise.

What do you think?

Are sprocs the most appropriate way to allow the decoupling of external apps from databases?


Why use stored procedures?

The public interface of a database

Alternative view point

Why I do not use stored procedures

Last modified: 2011-05-15


5 Responses to Stored procedures – why use them?

  1. adriana says:

    interesting article; for a novice database developer

  2. sql-troubles says:

    There are only three standard options for CRUD (create, read, update, delete) operations: the stored procedures, the views and the direct access to tables, the later, as you said is not recommendable. The stored procedures offer in theory better performance than the update through views, better security especially from the SQL Injection point of view, more flexibility driven in special from the use of procedural language which allows to handle complex logic, validation code or errors, to write dynamic SQL Scripts, a powerful tool in writing generic code that encapsulates business logic. Also views have their strength, though in what concerns CRUD operations, stored procedures are preferable, though this depends also on the requirements.

    Database objects like views, stored procedures and UDFs (user defined functions) allow to hide the complexity of a database from the consumers (users, applications), providing a level a separation between the world and database. Now a change occurred in the database could require, after case, not only to change the objects accessed by the users, but also some code in the applications itself. Calling a stored procedure from an external application resumes in providing stored procedures’ name, the parameters and eventually the data types associated with the respective parameters. Now when a new attribute is added to a stored procedure, you might want to change also the way it appears in the application, for example by binding it to an existing/new control, handling its styling, etc. This doesn’t save you entirely from the headaches of modifying the third party applications, though it reduces the overall maintenance. In other scenarios like changing the name of an attribute, changing the table structure, removing an attribute, the changes could be hidden from the consumers by writing adequate code in database itself.

  3. Thanks for all the responses – there have been a few on different LinkedIn groups – main thread being on Database Experts.

    There seemed to be general agreement that sprocs can be a good way of providing an ‘database interface’ to of decouple dbs from their clients/apps making it easier to manage change.

    Scott Wood asked to hear from developers – to get some ‘intellectual pushback’ as the groups where database related forums.

  4. Harold Stull says:

    Having worn a number of hats in my career, and I find it possible to agree with everyone on these issues. As a developer, I have butted heads with DA’s and DBA’s over slow implementation support and bizarre data structures. As a DBA and data architect, I have had to clean-up the results of massive insertion attacks, explain to customers why their reports and dashboards show contradictory results, and cope with inconsistent and incomplete business rule application.
    The friction comes from different perspectives. Development, especially Agile, needs to deliver functionality in a relatively short time, compared to the total life of the operational system. The data resources(databases, archives, documents) represent the state and history of the enterprise, a longer timelilne.
    Database technology is likely to be part of business systems for several more decades. Middleware is more transient, even though it provides immediate productivity gains (does anyone remember Tuxedo?).
    To argue against SPROCs because DBAs are slow to implement is to blame the technology for an organizational problem. SPROCs (views, parameter queries) are code. Their development and maintenance are part of the SDLC not an afterthought and should be have the same cycle of requirement, design, implementation, QA, and deployment as any other component.
    If corporate policy or architecture assigns functionality to the data layer and also establishes an Agile development process, then data development must adopt Agile methods.

    • Harold I agree.

      Database dev needs to be an integral part of SDLC.

      Your comment about the excuse used by some for not taking a database API approach – due to ‘DBAs being slow to implement’
      – particularly resonates.

      Not only does having a db API help take a more flexible approach to change – it can help reduce some of the friction.

      eg you have a user story to develop a report/UI screen. The dev writes unit tests for the required method signature/data that should be returned. Next they can create the appropriate db API – sproc or view – just enough to make the tests pass. A small set of dummy test data can be used in these sprocs/views. The dev can then continue. At a later stage the correct normalised base tables can be built.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: