How the SQL Pass-Through Facility Works

When you read and update DBMS data with the SQL pass-through facility, SAS/ACCESS passes SQL statements directly to the DBMS for processing. Here are the steps.

1. Invoke PROC SQL and submit a PROC SQL CONNECT statement that includes a DBMS name and the appropriate connection options to establish a connection with a specified database.
2. Use a CONNECTION TO component in a PROC SQL SELECT statement to read data from a DBMS table or view.

   In the SELECT statement (PROC SQL query) that you write, use the SQL that is native to your DBMS. SAS/ACCESS passes the SQL statements directly to the DBMS for processing. If the SQL syntax that you enter is correct, the DBMS processes the statement and returns any results to SAS. If the DBMS does not recognize the syntax that you enter, it returns an error that appears in the SAS log. The SELECT statement can be stored as a PROC SQL view. Here is an example.

   proc sql;
   connect to oracle (user=myusr1 password=mypwd1);
   create view budget2000 as select amount_b,amount_s 
      from connection to oracle 
      (select Budgeted, Spent from annual_budget);
   quit;

3. Use a PROC SQL EXECUTE statement to pass any dynamic, nonquery SQL statements (such as INSERT, DELETE, and UPDATE) to the database.

   As with the CONNECTION TO component, all EXECUTE statements are passed to the DBMS exactly as you submit them. INSERT statements must contain literal values. Here is an example.

   proc sql;
      connect to oracle(user=myusr1 password=mypwd1);
      execute (create view whotookorders as select ordernum, takenby, 
        firstname, lastname,phone from orders, employees 
        where orders.takenby=employees.empid) by oracle;
      execute (grant select on whotookorders to myusr1) by oracle;
   
   disconnect from oracle;
   quit;

4. End the connection with the DISCONNECT statement.

For more details, see the SQL pass-through facility.