You can use an SQL SHOW PROCEDURE statement to view and verify the parameter types:
CREATE PROCEDURE spSample2(OUT p1 INTEGER, INOUT p2 INTEGER, IN p3 INTEGER) BEGIN SET p1 = p3; SET p2 = p2 * p3; END;
The call arguments can be specified in any of the following formats:
Format 1:
CALL spSample2(p1, 20, 3);
where the parameter name p1 is supplied as an argument because it is a placeholder for an OUT parameter. The values 20 and 3 are passed as arguments to the INOUT and IN parameters, p2 and p3 respectively.
The CALL statement returns the following response:
p1 p2 ----------- ----------- 3 60
Format 2:
CALL spSample2(p1, 20 * 2, 30 + 40);
where the expressions (20 * 2) and (30 + 40) are passed as arguments to the INOUT and IN parameters, p2 and p3.
The CALL statement returns the following response:
p1 p2 ----------- ----------- 70 2800
Format 3:
CALL spSample2(CAST(CAST(p1 AS CHARACTER(10)) AS TITLE 'OutputValue'), CAST(20 AS SMALLINT), 30 + 40);
where the expressions CAST(20 AS SMALLINT) and (30 + 40) are passed as arguments to the INOUT and IN parameters, p2 and p3.
The CALL statement returns the following response:
OutputValue 20 ----------- ---------- 70 1400
Format 4:
This format is used with BTEQ and assumes that data is being imported from a data file.
USING (a INTEGER) CALL spSample1(p1, :a, 3);
where 3 is the value passed as an argument to the IN parameter p3. The value read from the input data file is assigned to the variable a and is passed as an argument to the INOUT parameter p2 via a USING clause.
BTEQ receives the following response from the Vantage platform:
p1 p2 ----------- ----------- 3 30
Format 5 (Using NAMED and TITLE phrases in the CALL request):
CALL spSample1(CAST (p1 AS NAMED AA TITLE 'OUT VALUE'), CAST (((20 * 2) + 3) AS TITLE 'INOUT VALUE'), 1);
The response looks like this:
OUT VALUE INOUT VALUE ----------- ------------ 1 43