Executing Stored Procedures (Database Engine)

To execute a stored procedure, use the Transact-SQL EXECUTE statement. Alternatively, you can execute a stored procedure without using the EXECUTE keyword if the stored procedure is the first statement in the batch.

Executing System Stored Procedures

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 Microsoft 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.

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

Database Collation Matching

Microsoft SQL Server 2005 uses the calling database collation when matching system procedure names. Therefore, you should always use the exact case of system procedure names in your application. For example, this code will fail if executed in the context of a database that has a case-sensitive collation:

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

Use thesys.system_objects and sys.system_parameters catalog views to display the exact system stored procedure names.

Executing System Extended Stored Procedures

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:\';

Executing User-defined Stored Procedures

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.

Specifying Parameters

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

If you supply parameters in the form **@parameter =**value, you can supply them in any order. You can also omit parameters for which defaults have been supplied. If you supply one parameter in the form **@parameter =**value, you must supply all subsequent parameters this way. If you do not supply parameters in the form **@parameter =**value, you must supply them in the order given in the CREATE PROCEDURE statement.

When executing a stored procedure, the server rejects any parameters that were not included with the parameter list during procedure creation. Any parameter passed by reference (explicitly passing the parameter name) is not accepted if the parameter name does not match.

Using Default Values in Parameters

Although you can omit parameters for which defaults have been supplied, you can only truncate the list of parameters. For example, if a stored procedure has five parameters, you can omit both the fourth and the fifth parameters, but you cannot skip the fourth and still include the fifth unless you supply parameters in the form **@parameter =**value.

The default value of a parameter, if defined for the parameter in the stored procedure, is used when:

• No value for the parameter is specified when the stored procedure is executed.
• The DEFAULT keyword is specified as the value for the parameter.

See Also

Concepts

Automatic Execution of Stored Procedures
Creating Stored Procedures (Database Engine)
Creating CLR Stored Procedures
Modifying Stored Procedures
Recompiling Stored Procedures
Viewing Stored Procedures
Deleting Stored Procedures

Other Resources

EXECUTE (Transact-SQL)
DECLARE @local\_variable (Transact-SQL)
SET @local\_variable (Transact-SQL)
Implementing Stored Procedures

Help and Information

Getting SQL Server 2005 Assistance