Ny alternative to stored procedures?


Is there any alternative to stored procedures, secure and fast as well as stored procs. i know only Hibernate. Is there any other technologies like that?

Best Solution

Stored procedures are a place to put code (SQL) which executes on the database, so I understand the question to mean

"is there any other way to package up the code which runs on the database?"

There are several answers:

  • There is nothing else that is quite the same as a stored procedure, but there are alternatives which you might consider.
  • You could write all your SQL as strings inside your client code (java or whatever)
    • This has various problems (loss of encapsulation, tight coupling -> harder maintenance), however, and is not a good idea.
  • You could use an ORM such as NHibernate, which inserts a layer between your client logic and the database. The ORM generates SQL to execute on the database. With an ORM, it is harder to express complex business logic than in a stored procedure (sweeping generalisation!).
  • A kind of halfway house is to define your own data access layer (DAL) in java (or watever you're using) and keep it separate from the main body of client code (separate classes / namespaces / etc.), so that your client makes calls to the DAL, and the DAL interprets these and sends SQL to the database, returning the results from the database back to the client.