SQL Stored Procedures

Stored procudures

1. A stored procedure is a subroutine available to applications that access a relational database system. A stored procedure (sometimes called a proc, sproc, StoPro, StoredProc, or SP) is actually stored in the database data dictionary.
2. Typical uses for stored procedures include data validation (integrated into the database) or access control mechanisms.
3. Furthermore, stored procedures can consolidate and centralize logic that was originally implemented in applications.
4. Extensive or complex processing that requires execution of several SQL statements is moved into stored procedures, and all applications call the procedures. One can use nested stored procedures, by executing one stored procedure from within another.
5. Stored procedures are great for processing complex SQL logic, securing and controlling access to data, and returning a rowset to a calling routine.
6. 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 statement.
7. A user defined function (UDF) is a prepared code segment that can accept parameters, process some logic, and then return some data. According to SQL Server Books Online, UDFs in SQL Server™ 2000 can accept anywhere from 0 to 1024 parameters, although I must confess I have never tried to pass 1024 parameters into a UDF. Another key characteristic of UDFs is that they return a value. Depending on the type of UDF, the value can be used by the calling routine to continue processing its data. Thus, if a UDF returns a single value (a scalar value), the calling routine can use that value anywhere a standard variable or a literal value can be used. If a UDF returns a rowset, the calling routine can loop through the rowset, join to it, or simply select columns from it.
8. Unlike views, stored procedures are compiled, making them ideal candidates to represent and process frequently run SQL statements.
9. Views are limited to only certain columns and rows from the underlying SELECT statement that generated the view. Thus a view is often used to represent a commonly used SELECT statement that may join several tables, employ a WHERE clause, and expose specific columns. Views are often found in the FROM clause of a SQL statement joined to other tables and views.
10. There are two main types of UDFs: scalar value-returning UDFs and table value-returning UDFs. Within table value UDFs you’ll find UDFs that return inline tables and multistatement tables.