| Lesson 8 | System 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:
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.
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
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++.
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.