Types of Stored Procedures
There are many types of stored procedures available in Microsoft SQL Server. This topic briefly describes each stored procedure type and includes an example of each.
User-defined Stored Procedures
Stored procedures are modules or routines that encapsulate code for reuse. A stored procedure can take input parameters, return tabular or scalar results and messages to the client, invoke data definition language (DDL) and data manipulation language (DML) statements, and return output parameters. In SQL Server 2008, a stored procedure can be of two types: Transact-SQL or CLR.
A Transact-SQL stored procedure is a saved collection of Transact-SQL statements that can take and return user-supplied parameters. For example, a stored procedure might contain the statements needed to insert a new row into one or more tables based on information supplied by the client application. Or, the stored procedure might return data from the database to the client application. For example, an e-commerce Web application might use a stored procedure to return information about specific products based on search criteria specified by the online user.
A CLR stored procedure is a reference to a Microsoft .NET Framework common language runtime (CLR) method that can take and return user-supplied parameters. They are implemented as public, static methods on a class in a .NET Framework assembly. For more information, see CLR Stored Procedures.
Extended Stored Procedures
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use CLR Integration instead.
Extended stored procedures let you create your own external routines in a programming language such as C. Extended stored procedures are DLLs that an instance of Microsoft SQL Server can dynamically load and run. Extended stored procedures run directly in the address space of an instance of SQL Server and are programmed by using the SQL Server Extended Stored Procedure API.
CLR Integration provides a more robust and secure alternative to writing extended stored procedures.
System Stored Procedures
Many administrative activities in SQL Server are performed through a special kind of procedure known as a system stored procedure. For example, sys.sp_changedbowner is a system stored procedure. System stored procedures are physically stored in the Resource database and have the sp_ prefix. System stored procedures logically appear in the sys schema of every system- and user-defined database. In SQL Server 2008, GRANT, DENY, and REVOKE permissions can be applied to system stored procedures. For a complete list of system stored procedures, see System Stored Procedures (Transact-SQL).
SQL Server supports the system stored procedures that provide an interface from SQL Server to external programs for various maintenance activities. These extended stored procedures use the xp_ prefix. For a complete list of extended stored procedures, see General Extended Stored Procedures (Transact-SQL).