ASA SQL Reference
  SQL Statements

EXECUTE statement [T-SQL]

Description 

Use Syntax 1 to invoke a procedure, as an Adaptive Server Enterprise-compatible alternative to the CALL statement. You can also execute statements within Transact-SQL stored procedures and triggers. For more information, see EXECUTE IMMEDIATE statement [SP]. Use Syntax 2 to execute a prepared SQL statement in Transact-SQL.

Syntax 1 

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

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

Syntax 2 

EXECUTE ( string-expression )

Usage 

Syntax 1 executes a stored procedure, optionally supplying procedure parameters and retrieving output values and return status information.

The EXECUTE statement is implemented for Transact-SQL compatibility, but can be used in either Transact-SQL or Watcom-SQL batches and procedures.

With Syntax 2, you can execute 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.

Permissions 

Must be the owner of the procedure, have EXECUTE permission for the procedure, or have DBA authority.

Side effects 

None.

See also 

CALL statement

EXECUTE statement [ESQL]

EXECUTE IMMEDIATE statement [SP]

Example 

The following procedure illustrates Syntax 1.

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 on the client window. If you are connected from an ODBC or Embedded SQL application, the messages are displayed on the database server 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