Types of Stored Procedures

There are many types of stored procedures:

• User-defined

• System

• Extended

• Temporary

• Global temporary

• Remote

• CLR

There are also database objects, which are very similar in nature:

• Triggers

• Views

• User-defined functions

As you can infer from the name, user-defined stored procedures are simply plain, stored procedures assembled by administrators or developers for later use. All the examples we have discussed so far in this chapter have been such stored procedures.

Microsoft delivers a vast set of stored procedures as part of SQL Server. They are designed to cover all aspects of system administration. Internally, system stored procedures are just regular stored procedures. Their special features result from the fact that they are stored in system databases (master and msdb) and they have the prefix sp_. This prefix is more than just a convention. It signals to the server that the stored procedure should be accessible from all databases without putting the database name as a prefix to fully qualify the name of the procedure. For example, you can use sp_spaceused to examine usage of the current database, such as disk space for data and indexes.

We will examine all types of stored procedures in more detail in Chapter 6.