EXECUTE Statement

For SQL Server 6.5 information, see EXECUTE Statement in What's New for SQL Server 6.5.

Executes a system procedure, a user-defined stored procedure, or an extended stored procedure. Also supports the execution of a character string within a Transact-SQL batch. In earlier releases, Transact-SQL statements needed to include the actual name of an object at parse and compile time. With the EXECUTE statement, a string can be created with variables that are resolved at execution time.

Syntax

To execute a stored procedure:

EXEC[ute]
{[@return_status =]
    {[[[server.]database.]owner.]procedure_name[;number] |
        @procedure_name_var}
    [[@parameter_name =] {value | @variable [OUTPUT]]
        [, [@parameter_name =] {value | @variable [OUTPUT]}]...]
    [WITH RECOMPILE]]

To execute a character string:

EXEC[ute] ({@str_var | 'tsql_string'} [{@str_var | 'tsql_string'}...)}

where

@return_status

Is an optional integer variable that stores the return status of a stored procedure. This variable must be declared in the batch or stored procedure before it is used in an EXECUTE statement.

procedure_name

Specifies a stored procedure. You can execute a procedure that has been created in another database if you are its owner or have permission to execute it in that database. You can execute a procedure on another SQL Server if you have permission to use that server (remote access) and to execute the procedure in that database. If you specify a server name but do not specify a database name, SQL Server looks for the procedure in your default database.

@procedure_name_var

Is the name of a locally defined variable that represents a stored procedure name.

;number

Is an optional integer used to group procedures of the same name so that they can be dropped together with a single DROP PROCEDURE statement. Procedures used in the same application are often grouped this way. For example, the procedures used with the application orders might be named orderproc;1, orderproc;2, and so on. The statement DROP PROCEDURE orderproc drops the entire group. Once procedures have been grouped, individual procedures within the group cannot be dropped. For example, the statement DROP PROCEDURE orderproc;2 is not allowed. This parameter is not used for extended stored procedures.

@parameter_name

Specifies the parameter for a procedure, as defined in the CREATE PROCEDURE statement. Parameter names must be preceded by the "at" symbol (@). When used with the @parameter_name = value form, parameter names and constants need not be supplied in the order defined in the CREATE PROCEDURE statement. However, if the @parameter_name = value form is used for any parameter, it must be used for all subsequent parameters.

value

Is the value of the parameter to the procedure. If you do not use parameter names, parameter values must be supplied in the order defined in the CREATE PROCEDURE statement.

If the value of a parameter is an object name, character string, or qualified by a database name or owner name, the entire name must be enclosed in single quotation marks. If the value of a parameter is a keyword, the keyword must be enclosed in double quotation marks.

If a default is defined in the CREATE PROCEDURE statement, a user can execute the procedure without specifying a parameter. The default must be a constant and can include the wildcard characters %, _, [ ], and [^] if the procedure uses the parameter name with the LIKE keyword. For details, see the CREATE PROCEDURE statement.

The default can also be NULL. Usually, the procedure definition specifies what action should be taken if a parameter value is NULL.

@variable

Specifies the variable that stores a return parameter.

OUTPUT

Indicates that the stored procedure returns a parameter. The matching parameter in the stored procedure must also have been created with the keyword OUTPUT.

WITH RECOMPILE

Forces a new plan to be compiled. Use this option if the parameter you're supplying is atypical or if the data has significantly changed. The changed plan is used in subsequent executions. This parameter is not used for extended stored procedures.

Note If you use SELECT * in your CREATE PROCEDURE statement, the procedure (even with the WITH RECOMPILE option to EXECUTE) does not select any new columns added to the table. So that the procedure can select new columns, you must drop the procedure and re-create it.

@str_var

Is the name of a local string variable. In most cases, this variable will be composed of multiple strings that in total will represent a Transact-SQL statement. The @str_var variable can be a char, character, varchar, or text parameter.

tsql_string

Represents a portion or complete string of Transact-SQL statement(s). The total concatenated string passed to the EXECUTE statement must include the complete Transact-SQL command with appropriate spaces and syntax. The string must be enclosed with single quotation marks (').

Remarks

You don't need to use the EXECUTE keyword while executing stored procedures if the statement is the first one in a batch.

You can supply parameters by using value or by using @parameter_name = value. A parameter is not part of a transaction, so if a parameter is changed in a transaction that is later rolled back, the parameter's value does not revert to its previous value. The value returned to the caller is always the value at the time the procedure returns.

If you are using OUTPUT parameters and intend to use the return values in other statements within the calling batch or procedure, the value of the parameter must be passed as a variable ¾ that is, @parameter_name = @variable. It is an error to execute a procedure specifying OUTPUT for a parameter not defined as an OUTPUT parameter in the CREATE PROCEDURE statement. You cannot pass constants to stored procedures using OUTPUT; the return parameter requires a variable name. You must declare the variable's datatype and assign it a value before executing the procedure. Return parameters can be of any datatype except text or image.

Nesting occurs when one stored procedure calls another. The nesting level is incremented when the called procedure begins execution, and it is decremented when the called procedure has finished. Exceeding the maximum of 16 nesting levels causes the whole calling procedure chain to fail. The current nesting level is stored in the @@NESTLEVEL global variable.

SQL Server currently uses return values 0 through -14 to indicate the execution status of stored procedures. Values from -15 through -99 are reserved for future use. For a list of values, see the RETURN statement in the Control-of-flow Language topic.

Remote stored procedures and extended stored procedures are not within the scope of a transaction, so commands executed through calls to them cannot be rolled back. For more information, see the Stored Procedures¾Extended Stored Procedures topic.

Permission

EXECUTE permission for a stored procedure defaults to the owner of the stored procedure, who can transfer it to other users. Permission to use the statement(s) within the EXECUTE string is checked at the time EXECUTE is encountered, even if the EXECUTE statement is included within a stored procedure. When a stored procedure is run that executes a string, the user running the procedure is checked for permissions. In other words, the procedure is checked in the context of the user executing the procedure, not in the context of the user who created the procedure.