Teradata Stored Procedures - Casting Call Arguments to Stored Procedure

Casting CALL arguments to Stored Procedure

Consider a Stored procedure as follows:

 

CREATE PROCEDURE  SUPPORT_PROCS.TEST_PROC

( IN var1 INTEGER, INOUT var2 integer ,OUT var3 INteger )

BEGIN

SET var2= var1 * var1;

SET var3=var1*var2        ;

END;

Things Learnt: The semicolon after each statement is important.

While creating this test procedure I forgot to put the highlighted semicolon and following is the error message I got:

SPL1027:E(L7), Missing/Invalid SQL statement'E(3707):Syntax error, expected something like ';' between the word 'var2' and the 'END' keyword.'.

SPL1048:E(L7), Unexpected text ';' in place of SPL statement.

SPL1027:E(L7), Missing/Invalid SQL statement'E(3707):Syntax error, expected something like an 'END' keyword between ';' and the end of the request.'.

In the above procedure we have one IN parameter, One INOUT parameter and one OUT parameter.

When we call the procedure the out put we get will have 2 columns: from INOUT and OUT parameter.

Following is how the output will normally look:

CALL SUPPORT_PROCS.TEST_PROC(3,1,varx);

1	var3
9	27

Things learnt: Note that for the Output parameter we pass  a dummy name. It does not actually do anything. But its necessary. If we don’t write that then the call statement will fail with following error:

CALL Failed. 3812:  The positional assignment list has too few values. 
 

Also for the INOUT type parameter we cannot give a dummy variable as we give for OUT parameter.
This is irrespective of whether INOUT parameter is actually used as an input and output.
In the above example even though the 2nd parameter is INOUT, its actually being used as a OUT paremeter.
So i thought following call should work.

CALL SUPPORT_PROCS.TEST_PROC(3,varx,vary)

However it failed with the error: CALL Failed. 5531:  Named-list is not supported for arguments of a procedure.

In such cases where you don’t have anything to actually send for an INOUT paremeter, you can send NULL using the keyword 'NULL' as follows:

CALL SUPPORT_PROCS.TEST_PROC(3,NULL,varx);

Null	var3
9	27

Another thing to note is that we cannot pass a value for a OUT type parameter.
Ex: In the following CALL I pass the value 1 for the output parameter and the call fails:

CALL SUPPORT_PROCS.TEST_PROC(3,NULL,1)

CALL Failed. 5492:  The argument for an INOUT/OUT parameter 'var3'  is invalid. 

So the Key things learnt about Calling a procedure are:

1. For a OUT type parameter we need to provide a dummy variable name.
2. For a OUT type parameter we cannot provide a value. The CALL will fail saying the Argument is invalid.
3. For a INOUT type parameter we cannot provide a dummy variable. We need to provide a value irrespective of whether its actually used in the procedure or not.
4. Some times providing a value that is not actually used anywhere in the procedure looks confusing. In such cases if there is no value to be provided for the INOUT parameter we can pass a NULL using 'NULL' keyword.

In the above output we can see that the column titles don’t look correct, also we may have a scenario where in the output from procedure needs to be casted to a specific data type or with a different column title or .

In such cases we use CAST as shown below.

Ex 1:

CALL support_procs.test_proc(1,CAST(2 AS NAMED SQUARE),CAST(ou1 AS NAMED CUBES))

SQUARE	CUBES
1	1

Note that in the above Cases the CAST applies to the OUTPUT and INOUT parameter and thus becomes the column names.

Ex 2:

CALL support_procs.test_proc(51,CAST(2 AS DECIMAL(10,0)),CAST(ou1 AS NAMED CUBES));

2	CUBES
2601	132651

In the above example the Output in the 1st column gets casted to DECIMAL(10,0).

So basically the CAST here is applied after the stored procedure completes its processing.

Ex 3:

CALL support_procs.test_proc(51,CAST(2 AS DECIMAL(10,2)),CAST(ou1 AS DECIMAL(10,2) NAMED CUBES));

2	CUBES
2601.00	132651.00

Any casting we apply for the OUT parameter applies to the output that comes from the stored procedure.

Any casting we apply for INOUT parameter applies to both input and output.

An IN, INOUT or OUT argument may be CAST to acquire any of the following attributes:

• Data type

• FORMAT

• TITLE

• NAMED
   

This also teaches us that CAST is not only used for changing data types, but also for changing Formats, Titles and names.