Types of Stored Procedures

In SQL Server, there are four types of stored procedures: system stored procedures, user-defined stored procedures, temporary stored procedures, and extended stored procedures. System and extended stored procedures are created automatically at installation time. The other types (user-defined, temporary) are the ones users create explicitly.

System Stored Procedures

System stored procedures are created automatically in system databases when you install SQL Server. They are basically a way to interact with system tables. Moreover, there is a system stored procedure for almost any administrative task you perform in SQL server. Also, because Microsoft doesn't recommend dealing directly with system tables, this is the preferred way to deal with them.

Every global system stored procedure's name has the sp_ prefix, and for this reason they can be executed from any database. Listing 8.1 demonstrates this feature, calling the sp_helpdb system stored procedure (which gives general information about databases) from the Northwind database.

Code Listing 8.1. Executing a System Stored Procedure (Which Is Stored in Master) from the Northwind Database

						
USE Northwind
GO

sp_helpdb

--The output has been simplified

name         db_size       owner     dbid   created     compatibility_level
------------ ------------- --------- ------ ----------- -------------------
master       12.19 MB      sa        1      Aug  6 2000 80
model        1.13 MB       sa        3      Aug  6 2000 80
msdb         13.50 MB      sa        4      Aug  6 2000 80
Northwind    3.94 MB       sa        6      Aug  6 2000 80
pubs         2.13 MB       sa        5      Aug  6 2000 80
tempdb       8.50 MB       sa        2      Jan 22 2001 80

Transact-SQL provides a system function, OBJECTPROPERTY,that is usedto check for a variety of object properties. Specifically, the property 'IsMSShipped' checks whether an object is a system object. Thus, it can be used to identify whether a stored procedure is a system stored procedure. This system function, like many others in SQL Server, receives the object's ID as a parameter, which can be obtained using the OBJECT_ID system function. The OBJECTPROPERTY function returns 0 if the property is true, or 1 if not. Listing 8.2 shows the use of this property.

Code Listing 8.2. Using the OBJECTPROPERTY System Function to Check Whether an Object Was Created During SQL Server Installation

						
USE Master
SELECT OBJECTPROPERTY(OBJECT_ID('sp_help'),'IsMSShipped')
GO
-----------
1

(1 row(s) affected)

User-Defined Stored Procedures

You create user-defined stored procedures in SQL Server to implement business logic. Any task, no matter how simple or complex, that comprises multiple statements and conditions can be programmed as a stored procedure, and then the calling application just needs to execute the stored procedure, instead of executing the whole set of statements separately.

User-defined stored procedures are created using the CREATE PROCEDURE statement, and then SQL Server stores them in the current database.

Stored procedures'names, like any other object's name, must be unique within the database and unique to the user who creates them (the owner). Hence, in a certain database, it is possible that two stored procedures exist with the same name but with different owners.

Any stored procedure that is created in the master database with the sp_ prefix—for example, sp_myprocedure—can be accessed from any other database. In general, when a stored procedure is executed and its name has the sp_ prefix, SQL Server looks for it, first in the current database, and then, if it's not found in the current database, SQL Server looks for it in the master database.

For example, you can create a user-defined stored procedure in master, as Listing 8.3shows, and call it from other databases.

Code Listing 8.3. Creation of a Stored Procedure, with the sp_ Prefix, in Master, and Execution in Pubs

						
USE Northwind
GO
CREATE PROCEDURE sp_showdatabasename
AS
SELECT 'Northwind'
GO

USE Master
GO
CREATE PROCEDURE sp_showdatabasename
AS
SELECT 'Master'
GO

-- When executed from Northwind, SQL Server executes
-- the one stored in Northwind
USE Northwind
EXEC sp_showdatabasename
GO
-- When executed from Pubs, SQL Server executes
-- the one stored in Master, because there isn't
-- a stored procedure called sp_showdatabasename
-- in the Pubs database
USE Pubs
EXEC sp_showdatabasename
GO
---------

Northwind

(1 row(s) affected)


------
Master

(1 row(s) affected)

Temporary Stored Procedures

These are stored procedures created by users and stored in the tempdb database. They are called temporary because they are dropped automatically by SQL Server, unless you explicitly issue a DROP PROCEDURE statement. Like any other temporaryobject in SQL Server, when creating temporary stored procedures, use the # prefix for local and the ## prefix for global temporary stored procedures. Listing 8.4 shows the creation of a temporary stored procedure. After executing the code shown in Listing 8.4 in Query Analyzer, expand the Stored Procedures folder of tempdb in the Object Browser, and you will see the stored procedure #getdatabasename listed. Then, close the current connection to SQL Server (close the window if you're working in Query Analyzer), and refresh the Stored Procedures folder of tempdb; the table will be gone.

Code Listing 8.4. Creation of a Temporary Stored Procedure

						
CREATE PROC #getdatabasename
AS
SELECT db_name() AS database_name
GO

Basically, temporary stored procedures have the same functionality as user-defined stored procedures, with one exception; they are dropped when the connection that creates them is finished.

Extended Stored Procedures

Extended stored procedures are DLL programs written in C++ that extend the capabilities of SQL Server. They are located in the master database. SQL Server has its own set of extended stored procedures whose name begins with xp_, which are used mainly for administrative purposes. However, there are some extended stored procedures that start with sp_ just to consider them as global—for example, sp_OACreate.

You can create your own extended stored procedure, coding a DLL using C++ and then adding it to SQL Server as an extended stored procedure, using the sp_addextendedproc system stored procedure. Be very careful when coding extended stored procedures (trap any kind of errors, deallocate memory, and so on) because they run in the same memory space as SQL Server; thus, any error in an extended stored procedure can crash SQL Server.