EXECUTE Statement

Sends data source-specific, non-query SQL statements to the data source.

Valid in:

SQL procedure steps.

Syntax

Details

Example

Syntax

EXECUTE (data-source-specific-SQL-statement(s))

<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;