Calling Stored Procedures in DB2 under UNIX and PC Hosts

Overview

A stored procedure is one or more SQL statements or supported third-generation languages (3GLs, such as C) statements that are compiled into a single procedure that exists in DB2. Stored procedures might contain static (hard-coded) SQL statements. Static SQL is optimized better for some DBMS operations. In a carefully managed DBMS environment, programmers and database administrators can know the exact SQL to execute.

SAS usually generates SQL dynamically. However, database administrators can encode static SQL in a stored procedure and therefore restrict SAS users to a tightly controlled interface. When you use a stored procedure call, you must specify a schema.

SAS/ACCESS support for stored procedure includes passing input parameters, retrieving output parameters into SAS macro variables, and retrieving the result set into a SAS table. Although DB2 stored procedures can return multiple result sets, SAS/ACCESS Interface to DB2 under UNIX and PC Hosts can retrieve only a single result set.

You can call stored procedures only from PROC SQL.

Examples

Example 1: Specify a Basic Call

Use CALL statement syntax to call a stored procedure.

call "schema".stored_proc

The simplest way to call a stored procedure is to use the EXECUTE statement in PROC SQL. In this example, you execute STORED_PROC by using a CALL statement. SAS does not capture the result set.

proc sql;
connect to db2 (db=sample uid= pwd=);
execute (call "schema".stored_proc);
quit;

Example 2: Specify One Input Parameter That Returns a Result Set

You can also return a result set. In this example, MYPROC3 is executed using a CALL statement and returns a result set.

proc sql;
connect to db2 (db=sample uid= pwd=);
%let p1 = 2000;
select * from connection to db2 (call MYSCHEMA.MYPROC3(:p1));

Example 3: Specify Three Output Parameters

The CALL statement syntax supports passing of parameters. You can specify such input parameters as numeric constants, character constants, or a null value. You can also pass input parameters by using SAS macro variable references. To capture the value of an output parameter, a SAS macro variable reference is required. This example uses a constant (1), an input/output parameter (:INOUT), and an output parameters (:OUT). Not only is the result set returned to the SAS results table, the SAS macro variables INOUT and OUT capture the parameter outputs.

proc sql;
connect to db2 (db=sample uid= pwd=);
%let INOUT=2;
create table sasresults as select * from connection to db2
   (call "schema".stored_proc (1,:INOUT,:OUT));
quit;

Example 4: Specify Three Different Parameters

The CALL statement syntax supports passing of parameters. To capture the value of an output parameter, a SAS macro variable reference is required. This example uses three output parameters (:p1, :p2, :p3:) and displays the value of each.

proc sql;
connect to db2 (db=sample uid= pwd=);
execute (call MYSCHEMA.MYPROC(:p1,:p2,:p3)) by db2;
%put &p1 &p2 &p3 /* display values of output parameters */

Example 5: Pass a NULL Parameter

In these calls, NULL is passed as the parameter to the DB2 stored procedure.

Null string literals in the call
```
call proc('');
call proc("")
```
Literal period or literal NULL in the call
```
call proc(.)
call proc(NULL)
```
SAS macro variable set to NULL string
```
%let charparm=;
call proc(:charparm)
```
SAS macro variable set to period (SAS numeric value is missing)
```
%let numparm=.;
call proc(:numparm)
```

Only the literal period and the literal NULL work generically for both DB2 character parameters and DB2 numeric parameters. For example, a DB2 numeric parameter would reject "" and %let numparm=.; would not pass a DB2 NULL for a DB2 character parameter. As a literal, a period passes NULL for both numeric and character parameters. However, when it is in a SAS macro variable, it constitutes a NULL only for a DB2 numeric parameter.

You cannot pass NULL parameters by omitting the argument. For example, you cannot use this call to pass three NULL parameters.

call proc(,,)

You could use this call instead.

call proc(NULL,NULL,NULL)

Example 6: Specify a Schema

Use standard CALL statement syntax to execute a stored procedure that exists in another schema, as shown in this example.

proc sql;
   connect to db2 (db=sample uid= pwd=);
   execute (call OTHERSCHEMA.MYPROC1(:p1));
quit;

If the schema is in mixed case or lowercase, enclose the schema name in double quotation marks.

proc sql;
connect to db2 (db=sample uid= pwd=);
execute (call "OTHERSCHEMA”.MYPROC1(:p1));
quit;

Example 7: Execute Remote Stored Procedures

If the stored procedure exists on a different DB2 instance, specify it with a valid three-part name.

select * from connection
proc sql;
connect to db2 (db=sample uid= pwd=);
to db2 (call MYSCHEMA.MYPROC1.prod5(:p1));
quit;