System stored procedures begin with the characters sp_. They are physically stored in the Resource database, but logically appear in the sys schema of every system- and user-defined database in the instance of SQL Server. System stored procedures can be executed from any database without having to fully quality the stored procedure name. A nonschema-qualified name is either a one-part name such as sp_someproc or a three-part name such as somedb..sp_someproc with the second part, the schema name, unspecified.

We recommend that you schema-qualify all system stored procedure names with the sys schema name to prevent name conflicts. The following example demonstrates the recommended method of executing a system stored procedure.

EXEC sys.sp_who;

The following examples demonstrate backward compatible methods of executing system stored procedures.

Note:
The following methods of executing system stored procedures will be removed in a future version of SQL Server. Avoid using these methods in new development work, and plan to modify applications that currently use them.

EXEC sp_who;
EXEC master.dbo.sp_who;
EXEC mydatabase..sp_who;
EXEC dbo.sp_who;
EXEC mydatabase.dbo.sp_who;

Database Collation Matching

exec SP_heLP; -- Will fail to resolve because SP_heLP does not equal sp_help 

System extended stored procedures begin with the characters xp_. They are physically stored in the Resource database, but logically appear in the sys schema of every system- and user-defined database in the instance of SQL Server. The following example demonstrates the recommended method for executing a system extended stored procedure.

EXEC sys.xp_subdirs 'c:\';

When executing a user-defined stored procedure (either in a batch or inside a module such as a user-defined stored procedure or function), we strongly recommend qualifying the stored procedure name with at least the schema name.

The following example demonstrates the recommended method for executing a user-defined stored procedure.

USE AdventureWorks;
GO
EXEC dbo.uspGetEmployeeManagers 50;

-Or-

EXEC AdventureWorks.dbo.uspGetEmployeeManagers 50;
GO

If a nonqualified user-defined stored procedure is specified, the Database Engine searches for the procedure in the following order:

• The sys schema of the current database.
  
• The caller's default schema if executed in a batch or in dynamic SQL. Or, if the nonqualified procedure name appears inside the body of another procedure definition, the schema containing this other procedure is searched next. For more information about default schemas, see User-Schema Separation.
  
• The dbo schema in the current database.
  

Important:
If any user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed if you use a nonschema-qualified name reference. For more information, see Creating Stored Procedures (Database Engine).

Parameter values can be supplied if a stored procedure is written to accept them.

The supplied value must be a constant or a variable; you cannot specify a function name as a parameter value. Variables can be user-defined or system variables such as @@spid.

The following examples demonstrate passing parameter values to the stored procedure uspGetWhereUsedProductID. The procedure expects values for two input parameters: a product ID and a date. The examples show how to pass parameters as constants and variables and also how to use a variable to pass the value of a function.

USE AdventureWorks;
GO
-- Passing values as constants.
EXEC dbo.uspGetWhereUsedProductID 819, '20050225';
GO
-- Passing values as variables.
DECLARE @ProductID int, @CheckDate datetime;
SET @ProductID = 819;
SET @CheckDate = '20050225';
EXEC dbo.uspGetWhereUsedProductID @ProductID, @CheckDate;
GO
-- Try to use a function as a parameter value.
-- This produces an error message.
EXEC dbo.uspGetWhereUsedProductID 819, GETDATE();
GO
-- Passing the function value as a variable.
DECLARE @CheckDate datetime;
SET @CheckDate = GETDATE();
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;
GO

If you want to specify the parameters in a different order than the order they are defined in the stored procedure, you must name them. For more information, see Specifying a Parameter Name.

To specify that a parameter should return a value to the calling program, use the OUTPUT keyword. For more information, see Specifying the Direction of a Parameter.

Specifying the Order of Parameters

Using Default Values in Parameters

Concepts

Other Resources

Help and Information