EXECUTE statement [T-SQL]

Invokes a procedure (an Adaptive Server Enterprise-compatible alternative to the CALL statement), executes a prepared SQL statement in Transact-SQL.

Syntax

Call a stored procedure

[ EXECUTE |  EXEC ][ @return_status = ] [creator.]procedure_name [ argument, ... ]

argument :
[ @parameter-name = ] expression
| [ @parameter-name = ] @variable [ output ]

Execute dynamic statements within T-SQL stored procedures and triggers
```
EXECUTE ( string-expression )
```

Remarks

The syntax for calling a stored procedure is implemented for Transact-SQL compatibility. EXECUTE calls a stored procedure, optionally supplying procedure parameters and retrieving output values and return status information. In Watcom SQL, use the CALL or EXECUTE IMMEDIATE statements.

With the syntax for executing statements, you can execute dynamic statements within Transact-SQL stored procedures and triggers. The EXECUTE statement extends the range of statements that can be executed from within procedures and triggers. It lets you execute dynamically prepared statements, such as statements that are constructed using the parameters passed in to a procedure. literal strings in the statement must be enclosed in single quotes, and the statement must be on a single line. This syntax is implemented for Transact-SQL compatibility, but can be used in either Transact-SQL or Watcom SQL batches and procedures.

The Transact-SQL EXECUTE statement does not have a way to signify that a result set is expected. One way to indicate that a Transact-SQL procedure returns a result set is to include something like the following:

IF 1 = 0
      SELECT 1 AS a

You can also execute statements within Transact-SQL stored procedures and triggers.

Privileges

When calling a procedure, you must be the owner of the procedure, or have the EXECUTE ANY PROCEDURE system privilege.

When executing dynamic statements within T-SQL stored procedures and triggers, the required privileges depend on the statement being executed.

Side effects

None.

Standards

ANSI/ISO SQL Standard
The syntax for calling store procedures is not in the standard.

The syntax for executing dynamic statements offers functionality equivalent to the EXECUTE IMMEDIATE statement in the ANSI/ISO SQL Standard, which is optional ANSI/ISO SQL Language Feature B031, "Basic dynamic SQL". However, the syntax for executing dynamic statements differs from that of the ANSI/ISO SQL Standard.

Example

The following procedure illustrates how to execute a stored procedure.

CREATE PROCEDURE p1( @var INTEGER = 54 )
AS
PRINT 'on input @var = %1!', @var
DECLARE @intvar integer
SELECT @intvar=123
SELECT @var=@intvar
PRINT 'on exit @var = %1!', @var;

The following statement executes the procedure, supplying the input value of 23 for the parameter. If you are connected from an Open Client or JDBC application, the PRINT messages are displayed in the client window. If you are connected from an ODBC or Embedded SQL application, the messages are displayed in the database server messages window.

EXECUTE p1 23;

The following is an alternative way of executing the procedure, which is useful if there are several parameters.

EXECUTE p1 @var = 23;

The following statement executes the procedure, using the default value for the parameter

EXECUTE p1;

The following statement executes the procedure, and stores the return value in a variable for checking return status.

EXECUTE @status = p1 23;