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:

USE Northwind;
GO
IF OBJECT_ID('dbo.usp_GetSortedShippers') IS NOT NULL
  DROP PROC dbo.usp_GetSortedShippers;
GO
-- Stored procedure usp_GetSortedShippers
-- Returns shippers sorted by requested sort column
CREATE PROC dbo.usp_GetSortedShippers
  @colname AS sysname = NULL
AS

DECLARE @msg AS NVARCHAR(500);

-- Input validation
IF @colname IS NULL
BEGIN
  SET @msg = N'A value must be supplied for parameter @colname.';
  RAISERROR(@msg, 16, 1);
  RETURN;
END

IF @colname NOT IN(N'ShipperID', N'CompanyName', N'Phone')
BEGIN
  SET @msg =
    N'Valid values for @colname are: '
    + N'N''ShipperID'', N''CompanyName'', N''Phone''.';
  RAISERROR(@msg, 16, 1);
  RETURN;
END

-- Return shippers sorted by requested sort column
IF @colname = N'ShipperID'
  SELECT ShipperID, CompanyName, Phone
  FROM dbo.Shippers
  ORDER BY ShipperID;
ELSE IF @colname = N'CompanyName'
  SELECT ShipperID, CompanyName, Phone
  FROM dbo.Shippers
  ORDER BY CompanyName;
ELSE IF @colname = N'Phone'
  SELECT ShipperID, CompanyName, Phone
  FROM dbo.Shippers
  ORDER BY Phone;
GO

The stored procedure accepts a column name from the Shippers table in the Northwind database as input (@colname); after input validation, it returns the rows from the Shippers table sorted by the specified column name. Input validation here involves verifying that a column name was specified, and that the specified column name exists in the Shippers table. Later in the chapter, I will discuss the subject of parameterizing sort order in more detail; for now, I just wanted to provide a simple example of a user-defined stored procedure. Run the following code to invoke usp_GetSortedShippers specifying N′CompanyName′ as input, generating the output shown in Table 7-1:

USE Northwind;
EXEC dbo.usp_GetSortedShippers @colname = N'CompanyName';

You can leave out the keyword EXEC if the stored procedure is the first statement of a batch, but I recommend using it all the time. You can also omit the stored procedure’s schema name (dbo in our case), but when you neglect to specify it, SQL Server must resolve the schema. The resolution in SQL Server 2005 occurs in the following order (adapted from Books Online):

As an example, suppose that you connect to the Northwind database and your user’s default schema in Northwind is called schema1. You invoke the following code in a batch:

EXEC usp_GetSortedShippers @colname = N'CompanyName';

The resolution takes place in the following order:

Besides the potential for confusion and ambiguity when not specifying the schema, there’s also an important performance reason to always specify it. When many connections are simultaneously running the same stored procedure, they may begin to block each other due to compile locks that they need to obtain when the schema name is not specified.

More Info

For more information about this problem, please refer to Knowledge Base Article ID 263889, "Description of SQL blocking caused by compile locks," at http://support.microsoft.com/?id=263889.

As I mentioned earlier, stored procedures can be used as a security layer. You can control access to objects by granting execution permissions on stored procedures and not to underlying objects. For example, suppose that there’s a database user called user1 in the Northwind database. You want to allow user1 to invoke the usp_GetSortedShippers procedure, but you want to deny user1 from accessing the Shippers table directly. You can achieve this by granting the user with EXECUTE permissions on the procedure, and denying SELECT (and possibly other) permissions on the table, as in:

DENY SELECT ON dbo.Shippers TO user1;
GRANT EXECUTE ON dbo.usp_GetSortedShippers TO user1;

SQL Server will allow user1 to execute the stored procedure. However, if user1 attempts to query the Shippers table directly:

SELECT ShipperID, CompanyName, Phone
FROM dbo.Shippers;

SQL Server will generate the following error:

Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Shippers', database 'Northwind', schema 'dbo'.

This security model gives you a high level of control over the activities that users will be allowed to perform.

I’d like to point out other aspects of stored procedure programming through the usp_GetSortedShippers sample procedure:

Now let’s get back to the focus of this section–user-defined stored procedures.

As I mentioned earlier, to invoke a user-defined stored procedure, you specify EXEC, the schema-qualified name of the procedure, and the parameter values for the invocation if there are any. References in the stored procedure to system and user object names that are not fully qualified (that is, without the database prefix) are always resolved in the database in which the procedure was created. If you want to invoke a user-defined procedure created in another database, you must database-qualify its name. For example, if you are connected to a database called db1 and want to invoke a stored procedure called usp_Proc1, which resides in db2, you would use the following code:

USE db1;
EXEC db2.dbo.usp_Proc1 <arguments>;

Invoking a procedure from another database wouldn’t change the fact that object names that are not fully qualified would be resolved in the database in which the procedure was created (db2, in this case).

If you want to invoke a remote stored procedure residing in another instance of SQL Server, you would use the fully qualified stored procedure name, including the linked server name: server.database.schema.proc.

When done, run the following code for cleanup:

USE Northwind;
GO
IF OBJECT_ID('dbo.usp_GetSortedShippers') IS NOT NULL
  DROP PROC dbo.usp_GetSortedShippers;

By "special stored procedure," I mean a stored procedure created with a name beginning with sp_ in the master database. A stored procedure created in this way has special behavior.

Important

Note that Microsoft strongly recommends against creating your own stored procedures with the sp_ prefix. This prefix is used by SQL Server to designate system stored procedures. In this section, I will create stored procedures with the sp_ prefix to demonstrate their special behavior.

As an example, the following code creates the special procedure sp_Proc1, which prints the database context and queries the INFORMATION_SCHEMA.TABLES view–first with dynamic SQL, then with a static query:

SET NOCOUNT ON;
USE master;
GO

IF OBJECT_ID('dbo.sp_Proc1') IS NOT NULL
  DROP PROC dbo.sp_Proc1;
GO

CREATE PROC dbo.sp_Proc1
AS
PRINT 'master.dbo.sp_Proc1 executing in ' + DB_NAME();

-- Dynamic query
EXEC('SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ''BASE TABLE'';'),

-- Static query
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';
GO

One of the unique aspects of a special procedure is that you don’t need to database-qualify its name when connected to another database. For example, you can be connected to Northwind and still be able to run it without database-qualifying its name:

USE Northwind;
EXEC dbo.sp_Proc1;

The PRINT command returns ′master.dbo.sp_Proc1 executing in Northwind′. The database name in the printed message was obtained by the DB_NAME function. It seems that DB_NAME "thinks" that the database context is Northwind (the current database) and not master. Similarly, dynamic SQL also assumes the context of the current database; so the EXEC command (which invokes a query against INFORMATION_SCHEMA.TABLES) returns table names from the Northwind database. In contrast to the previous two statements, the static query against INFORMATION_SCHEMA.TABLES seems to "think" that it is running in master–it returns table names from the master database and not Northwind. Similarly, if you refer with static code to user objects (for example, a table called T1), SQL Server will look for them in master. If that’s not confusing enough, in SQL Server 2000, static code referring to system tables (for example, sysobjects) was resolved in the current database. SQL Server 2005 preserves this behavior with the corresponding backward compatibility views (for example, sys.sysobjects)–but not with the new catalog views (for example, sys.objects).

Interestingly, the sp_ prefix works magic also with other types of objects besides stored procedures.

Caution

The behavior described in the following section is undocumented, and you should not rely on it in production environments.

For example, the following code creates a table with the sp_ prefix in master:

USE master;
GO
IF OBJECT_ID('dbo.sp_Globals') IS NOT NULL
  DROP TABLE dbo.sp_Globals;
GO

CREATE TABLE dbo.sp_Globals
(
  var_name sysname     NOT NULL PRIMARY KEY,
  val      SQL_VARIANT NULL
);

And the following code switches between database contexts, and it always manages to find the table even though the table name is not database-qualified.

USE Northwind;
INSERT INTO dbo.sp_Globals(var_name, val)
  VALUES('var1', 10);
USE pubs;
INSERT INTO dbo.sp_Globals(var_name, val)
  VALUES('var2', CAST(1 AS BIT));
USE tempdb;
SELECT var_name, val FROM dbo.sp_Globals;

The last query produces the output shown in Table 7-2.

For cleanup, run the following code:

USE master;
GO
IF OBJECT_ID('dbo.sp_Globals') IS NOT NULL
  DROP TABLE dbo.sp_Globals;

Do not drop sp_Proc1 yet because it is used in the following section.

System stored procedures are procedures that were shipped by Microsoft. In SQL Server 2000, system stored procedures resided in the master database, had the sp_ prefix, and were marked with the "system" (MS Shipped) flag. In SQL Server 2005, system stored procedures reside physically in an internal hidden Resource database, and they exist logically in every database.

A special procedure (sp_ prefix, created in master) that is also marked as a system procedure gets additional unique behavior. When the installation scripts that are run by SQL Server’s setup program create system procedures, they mark those procedures as system using the undocumented procedure sp_MS_marksystemobject.

Caution

You should not use the sp_MS_marksystemobject stored procedure in production because you won’t get any support if you run into trouble with them. Also, there’s no guarantee that the behavior you get by marking your procedures as system will remain the same in future versions of SQL Server, or even future service packs. Here, I’m going to use it for demonstration purposes to show additional behaviors that system procedures have.

Run the following code to mark the special procedure sp_Proc1 also as a system procedure:

USE master;
EXEC sp_MS_marksystemobject 'dbo.sp_Proc1';

If you now run sp_Proc1 in databases other than master, you will observe that all code statements within the stored procedure assume the context of the current database:

USE Northwind;
EXEC dbo.sp_Proc1;
USE pubs;
EXEC dbo.sp_Proc1;
EXEC Northwind.dbo.sp_Proc1;

As a practice, avoid using the sp_ prefix for user-defined stored procedures. Remember that if a local database has a stored procedure with the same name and schema as a special procedure in master, the user-defined procedure will be invoked. To demonstrate this, create a procedure called sp_Proc1 in Northwind as well:

USE Northwind;
GO
IF OBJECT_ID('dbo.sp_Proc1') IS NOT NULL
  DROP PROC dbo.sp_Proc1;
GO

CREATE PROC dbo.sp_Proc1
AS
PRINT 'Northwind.dbo.sp_Proc1 executing in ' + DB_NAME();
GO

If you run the following code, you will observe that when connected to Northwind, sp_Proc1 from Northwind was invoked:

USE Northwind;
EXEC dbo.sp_Proc1;
USE pubs;
EXEC dbo.sp_Proc1;

Drop the Northwind version because it would interfere with the following examples:

USE Northwind;
GO
IF OBJECT_ID('dbo.sp_Proc1') IS NOT NULL
  DROP PROC dbo.sp_Proc1;

Interestingly, system procedures have an additional unique behavior. They also resolve user objects in the current database, not just system objects. To demonstrate this, run the following code to re-create the sp_Proc1 special procedure, which queries a user table called Orders, and to mark the procedure as system:

USE master;
GO
IF OBJECT_ID('dbo.sp_Proc1') IS NOT NULL
  DROP PROC dbo.sp_Proc1;
GO

CREATE PROC dbo.sp_Proc1
AS
PRINT 'master.dbo.sp_Proc1 executing in ' + DB_NAME();
SELECT OrderID FROM dbo.Orders;
GO

EXEC sp_MS_marksystemobject 'dbo.sp_Proc1';

Run sp_Proc1 in Northwind, and you will observe that the query ran successfully against the Orders table in Northwind:

USE Northwind;
EXEC dbo.sp_Proc1;

Make a similar attempt in pubs:

USE pubs;
EXEC dbo.sp_Proc1;
master.dbo.sp_Proc1 executing in pubs
Msg 208, Level 16, State 1, Procedure sp_Proc1, Line 5
Invalid object name 'dbo.Orders'.

The error tells you that SQL Server looked for an Orders table in pubs but couldn’t find one.

When you’re done, run the following code for cleanup:

USE master;
GO
IF OBJECT_ID('dbo.sp_Proc1') IS NOT NULL
  DROP PROC dbo.sp_Proc1;
GO
USE Northwind
GO
IF OBJECT_ID('dbo.sp_Proc1') IS NOT NULL
  DROP PROC dbo.sp_Proc1;

SQL Server also supports other types of stored procedures: