All notes
DbComm

Stored procedures, database triggers, and UDFs

Stored procedure

A stored procedure is a subroutine available to applications that access a relational database management system (RDMS). Such procedures are stored in the database data dictionary.

Typical uses for stored procedures include data validation (integrated into the database) or access control mechanisms. Furthermore, stored procedures can consolidate and centralize logic that was originally implemented in applications.

Stored procedures are similar to user-defined functions (UDFs). The major difference is that UDFs can be used like any other expression within SQL statements, whereas stored procedures must be invoked using the CALL/EXECUTE statement. A procedure is not an expression and, thus, cannot be used like user-defined functions.

Stored procedures may return result sets, i.e., the results of a SELECT statement. Such result sets can be processed using cursors, by other stored procedures, by associating a result set locator, or by applications.

SO: stored procedure VS ORM. Stored Procedure are faster than SQL statements because they are pre-compiled in the Database Engine, with execution plans cached. You can't do that in ORM, but you have other alternatives, like using Cache Level 1 or 2. Also, try to do bulk operations with ORM. Stored Procedures works very well in those cases.

Concepts

Cursor

Wikipedia.

A database cursor is a control structure that enables traversal over the records in a database. Cursors facilitate subsequent processing in conjunction with the traversal, such as retrieval, addition and removal of database records. The database cursor characteristic of traversal makes cursors akin to the programming language concept of iterator.

Cursors allocate resources on the server, such as locks, packages, processes, and temporary storage. For example, Microsoft SQL Server implements cursors by creating a temporary table and populating it with the query's result set. If a cursor is not properly closed (deallocated), the resources will not be freed until the SQL session (connection) itself is closed.