The SQL Pass-Through Facility for Relational Databases

EXECUTE Statement

Sends DBMS-specific, non-query SQL statements to the DBMS

Valid in:

PROC SQL steps (when accessing DBMS data using SAS/ACCESS software)

Syntax
Arguments
Useful Statements to Include in EXECUTE Statements

Syntax

EXECUTE (dbms-specific-sql-statement) BY dbms-name | alias;

The EXECUTE statement sends dynamic non-query, DBMS-specific SQL statements to the DBMS and processes those statements.

In some SAS/ACCESS interfaces, you can issue an EXECUTE statement directly without first explicitly connecting to a DBMS. (See CONNECT statement.) If you omit the CONNECT statement, an implicit connection is performed (by using default values for all database connection arguments) when the first EXECUTE statement is passed to the DBMS. See the documentation for your SAS/ACCESS interface for details.

The EXECUTE statement cannot be stored as part of an SQL pass-through facility query in a PROC SQL view.

Arguments

(dbms-specific-sql-statement)

a dynamic non-query, DBMS-specific SQL statement. This argument is required and must be enclosed in parentheses. However, the SQL statement cannot contain a semicolon because a semicolon represents the end of a statement in SAS. The SQL statement might be case sensitive, depending on your DBMS, and it is passed to the DBMS exactly as you type it.

On some DBMSs, this argument can be a DBMS stored procedure. However, stored procedures with output parameters are not supported in the SQL pass-through facility. Furthermore, if the stored procedure contains more than one query, only the first query is processed.

Any return code or message that is generated by the DBMS is available in the macro variables SQLXRC and SQLXMSG after the statement executes. See Macro Variables for Relational Databases for more information about these macro variables.

dbms-name

identifies the database management system to which you direct the DBMS-specific SQL statement. The keyword BY must appear before the dbms-name argument. You must specify either the DBMS name for your SAS/ACCESS interface or an alias.

alias

specifies an alias that was defined in the CONNECT statement. (You cannot use an alias if the CONNECT statement was omitted.)

Useful Statements to Include in EXECUTE Statements

You can pass these statements to the DBMS by using the SQL pass-through facility EXECUTE statement.

CREATE: creates a DBMS table, view, index, or other DBMS object, depending on how the statement is specified.
DELETE: deletes rows from a DBMS table.
DROP: deletes a DBMS table, view, or other DBMS object, depending on how the statement is specified.
GRANT: gives users the authority to access or modify objects such as tables or views.
INSERT: adds rows to a DBMS table.
REVOKE: revokes the access or modification privileges that were given to users by the GRANT statement.
UPDATE: modifies the data in one column of a row in a DBMS table.

For more information and restrictions on these and other SQL statements, see the SQL documentation for your DBMS.

Top of Page