Stored Procedures  «Prev  Next»

Lesson 8System and extended stored procedures
Objective Describe system and extended stored procedures.

System and Extended Stored Procedures

Up to this point, you have been creating, altering, and deleting user-defined stored procedures. This lesson will examine the other types of stored procedures: system and extended.

System Stored Procedures

System stored procedures are preinstalled and aid in manipulating SQL Server objects, permissions, or other functionality. System stored procedures generally reside in the master database, and cannot be modified. All system stored procedures begin with the sp_ prefix.

System stored example

The sp_help system stored procedure returns information about any SQL Server object. It is used with a single argument, which is the name of the object. The following statement

sp_help sysobjects 

would give additional information about the sysobjects table and yields something like this:
System stored example
System stored example

This is similar to a user-defined stored procedure.
This is because the only real difference between system stored procedures and user-defined stored procedures is that system stored procedures are created by Microsoft instead of by you. Other than that, they are basically the same.
Hundreds of system stored procedures are available in SQL Server 2012. You can get information about all SQL Server system stored procedures through SQL Server 2012 Books Online.

Extended stored procedures

Extended stored procedures are not preinstalled and reside in an external DLL. Extended stored procedures are available through the master database only. With extended stored procedures, you can provide functionality that does not natively exist in SQL Server, but can be called from SQL Server. Extended stored procedures begin with the xp_ prefix.
Some extended stored procedures come with SQL Server 2012 and can be installed during the installation of SQL Server 2012. You can create extended stored procedures by creating a DLL using any Windows programming language, like Visual Basic or Visual C++.

Extended Stored Example

The xp_sendmail extended stored procedure sends email to contacts that you designate using the message and/or attachments that you specify. For example, you can send email to an administrator if a process fails. Although the ability to send mail is outside the realm of SQL Server 2012, the extended stored procedure allows this because it connects to your mail server, if one exists.

Review

The following table provides a quick review of the key characteristics of system and extended stored procedures:

  System Extended
Characteristic    
Availability Preinstalled Usually not preinstalled
Location Master database An external DLL
Customization Cannot modify Can modify, but only if you have the source code for the DLL
Prefix sp_ xp_
Example sp_help xp_sendmail

In the next lesson, you will learn about error handling in your stored procedures.