Features of SQL procedures

Implementing SQL procedures can play an essential role in database architecture, database application design, and in database system performance.

SQL procedures are characterized by many features. SQL procedures:
  • Can contain SQL Procedural Language statements and features which support the implementation of control-flow logic around traditional static and dynamic SQL statements.
  • Are supported in the entire Db2® family brand of database products in which many if not all of the features supported in Db2 Version 9 are supported.
  • Are easy to implement, because they use a simple high-level, strongly typed language.
  • SQL procedures are more reliable than equivalent external procedures.
  • Adhere to the SQL99 ANSI/ISO/IEC SQL standard.
  • Support input, output, and input-output parameter passing modes.
  • Support a simple, but powerful condition and error-handling model.
  • Allow you to return multiple result sets to the caller or to a client application.
  • Allow you to easily access the SQLSTATE and SQLCODE values as special variables.
  • Reside in the database and are automatically backed up and restored.
  • Can be invoked wherever the CALL statement is supported.
  • Support nested procedure calls to other SQL procedures or procedures implemented in other languages.
  • Support recursion.
  • Support savepoints and the rolling back of executed SQL statements to provide extensive transaction control.
  • Can be called from triggers.

SQL procedures provide extensive support not limited to the features listed previously.