Types of Stored Procedures

SQL Server 2005 supports different types of stored procedures: user-defined, system, and extended. You can develop user-defined stored procedures with T-SQL or with the CLR. This section briefly covers the different types.

User-Defined Stored Procedures

A user-defined stored procedure is created in a user database and typically interacts with the database objects. When you invoke a user-defined stored procedure, you specify the EXEC (or EXECUTE) command and the stored procedure’s schema-qualified name, and arguments:

EXEC dbo.usp_Proc1 <arguments>;

As an example, run the code in Example 7-1 to create the usp_GetSortedShippers stored procedure in the Northwind database:

Example 7-1. Creation Script for usp_GetSortedShippers ...

Get Inside Microsoft® SQL Server™ 2005 now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.