EXECUTE Statement

Sends data source-specific, non-query SQL statements to the data source.
Valid in: SQL procedure steps.

Syntax

EXECUTE (data-source-specific-SQL-statement(s))
BY
<data-source-name> <alias>

Syntax Description

Data-source-specific-SQL-statements
specifies a dynamic non-query, data source-specific SQL statement. Depending on your data source, the SQL statement can be case sensitive. The statement is passed to the data source exactly as you type it.
CREATE
creates a data source table, view, index, or other data source object, depending on how the statement is specified.
DELETE
deletes rows from a data source table.
DROP
drops a data source table, view, or other data source object, depending on how the statement is specified.
GRANT
gives users the authority to access or modify objects such as tables or views.
INSERT
inserts rows to a data source table.
REVOKE
revokes the access or modification privileges that were given to users by the GRANT statement.
UPDATE
updates the data in the specified columns of a row in a data source table.
Requirements:At least one statement is required.

The statement must be enclosed in parentheses.

Data-source-name
specifies the data-source-name to which you direct the data source-specific SQL statements. The EXECUTE statement's data-source-name must match the data-source-name specified in the CONNECT statement.
Alias
specifies the data source alias that was defined in the CONNECT statement. The EXECUTE statement's alias must match the alias that you specified in the CONNECT statement.

Details

The EXECUTE statement sends dynamic non-query, data source-specificSQL statements to the data source and processes those statements. The EXECUTE statement cannot be stored as part of a pass-through facility query in an SQL view.
The contents of the SQLXRC and SQLXMSG macro variables can be written to the SAS log using the macros. The contents are reset after each pass-through facility statement is executed.

Example: Drop and Create a Table and Insert a Data Row

Use the EXECUTE statement to drop a table, create a table, and insert a row of data after the connection:
EXECUTE(DROP table  ` My Invoice ` ) BY db;
EXECUTE(CREATE table  ` My Invoice ` (
 ` Invoice Number `  LONG not null,
 ` Billed To `  VARCHAR(20),
 ` Amount `  CURRENCY,
 ` BILLED ON `  DATETIME)) BY db;
EXECUTE(INSERT INTO  ` My Invoice ` 
values( 12345, 'John Doe', 123.45, #11/22/2003#)) BY db;