4.9.1. General Characteristics of Stored Procedures

As noted in 4.6. Enabling Versioned Views, database views allow useful queries to be saved and reused as though they were database tables. The SQL queries used in a view can be complex and can include data from other views such as those described in 4.8. Composite Views. However, the definition of each view is defined by a single SQL query.

Stored procedures and functions provide an another way to define reusable resources in a database. The key difference between these database views, stored procedures and functions are summarized in ¹ . From a practical perspective these differences enable stored procedures to facilitate some types of access to a SNOMED CT data that cannot be supported by using database views. The following subsections describe a few examples of stored procedures that are included in the SNOMED CT example database.

Feature	Database View	Stored Procedure	Stored Function
Enable definition of reusable resources that facilitate commonly required processes that access data without
Defined by a single query
Produce output that can queried in the same way as a database table
Can be defined to output the results of a single SQL query
Can be defined to output the results of one or more SQL queries
Can be defined to add, delete or alter data in a table2
Can be defined to include transactional SQL statements2
Can create, alter or delete database tables, views, procedures or functions2
Can be defined with input parameters to be set when invoked with values that affect the results
Can be defined to set values the values of one or more output parameters
Can be defined to return a single value of a specified datatype