EXECUTE Statement

EXECUTE Statement

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

Contains DBMS-specific arguments

Optional statement

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 your DBMS chapter for details.

The EXECUTE statement cannot be stored as part of an SQL Procedure 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 the SAS System. The SQL statement may be case-sensitive, depending on your DBMS, and it is passed to the DBMS exactly as you type it.

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 SQL Procedure Pass-Through Facility Return Codes for more information on these macro variables.

dbms-name

identifies the database management system to which you direct the DBMS-specific SQL statement. The name for your DBMS is listed in your DBMS chapter. The keyword BY must appear before the dbms-name argument. You must specify either a DBMS name or an alias in the EXECUTE statement.

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

This section lists some of the statements that you can pass to the DBMS by using the SQL Procedure Pass-Through Facility's EXECUTE statement.

CREATE: creates a DBMS table, view, index, or other DBMS objects, depending on how the statement is specified.
DELETE: deletes rows from a DBMS table.
DROP: deletes a DBMS table, view, or other DBMS objects, 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 columns of a row in a DBMS table.

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

The following example grants UPDATE and INSERT authority to user TESTUSER on the Oracle Rdb table ORDERS. Because the CONNECT statement is omitted, an implicit connection, that uses the default database, is made to Oracle Rdb.

proc sql;
   execute (grant update, insert on orders
     to [qa,testuser]) by rdb;
%put &sqlxmsg;

Chapter Contents
Previous
Next
Top of Page