Executing a Stored Procedure

Executing a Stored Procedure

SQL Server 2000

When you have to execute a stored procedure, use the Transact-SQL EXECUTE statement. You can execute a stored procedure without using the EXECUTE keyword if the stored procedure is the first statement in the batch.

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

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

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.

To execute a stored procedure that is grouped with other stored procedures of the same name, specify the identification number of the stored procedure within the group. For example, to execute the second stored procedure in the group my_proc, execute:

EXECUTE my_proc;2

To execute a stored procedure


See Also


Execution Characteristics of Extended Stored Procedures

SQL Profiler Scenarios

Specifying Parameters

Debugging an Extended Stored Procedure

SQL Stored Procedures

Returning Data Using a Return Code

Recompiling a Stored Procedure

© 2016 Microsoft