Teradata Stored Procedures - Calling a Procedure within a Procedure

Calling Stored Procedures from another stored procedure:

We can call procedures with in a procedure. We can also pass parameters to it. Say we have two procedures as follows: REPLACE  PROCEDURE SUPPORT_PROCS.SQUARE_IT(IN VALUE1 integer,OUT squarevalue INTEGER) BEGIN set squarevalue = value1 * value1; END; CREATE  PROCEDURE SUPPORT_PROCS.CUBE_IT(IN VALUE1 integer,OUT cubevalue INTEGER) BEGIN set cubevalue = value1 * value1 * value1; END; Each of the procedures accepts a parameter and returns a value. SUPPORT_PROCS.SQUARE_IT provides the square. SUPPORT_PROCS.CUBE_IT provides the square. Then we create a procedure which calls these two procedures. The procedure uses the SQUARE_IT and CUBE_IT to accept a value and return square and cube. REPLACE PROCEDURE SUPPORT_PROCS.CALCULATE(IN value1 integer ,OUT squarevalue integer, OUT cubevalue integer) BEGIN CALL SUPPORT_PROCS.SQUARE_IT(:value1,:squarevalue); CALL SUPPORT_PROCS.CUBE_IT(:value1,:cubevalue); END; CREATE PRECEDURE and REPLACE PROCEDURE both mean the same. When using REPLACE, if the procedure already exists it replaces it and if it does not then it creates one. Things Learnt: When I ran the query to create the CALCULATE procedure the query fails saying owner does not have execute access on the procedures SQUARE_IT and CUBE_IT. So we need to provide the access as follows: GRANT EXECUTE PROCEDURE ON SUPPORT_PROCS.SQUARE_IT TO SUPPORT_PROCS WITH GRANT OPTION; GRANT EXECUTE PROCEDURE ON SUPPORT_PROCS.CUBE_IT TO SUPPORT_PROCS WITH GRANT OPTION; Note that the access needed is called 'EXECUTE PROCEDURE' access even when do don’t actually 'EXEC' a procedure, we CALL them. Also note that we need to provide access to the owner SUPPORT_PROCS even when all procedures are under same database. We call the procedure CALCULATE as Follows: CALL SUPPORT_PROCS.CALCULATE(11,val1,val2); squarevalue cubevalue 121 1331 Rules for CALLing a procedure within a procedure: Stored procedures are callable only from the platform they were created. Windows or UNIX. Procedures cannot call them selves. Procedures can be only called from the session modes in which they were created - ANSI or Teradata. If call statement is inside a MACRO then it must be only one statement in the macro. CALL statement may not be a part of  a multi statement request. Number of nested call statement is limited to 15

Teradata Stored Procedures: Passing Arguments using Host variables (passing from a file)

Passing  data from file as arguments to Stored procedures:  Using host Variables.

Arguments to the stored procedure can come from a Data file. USING clause of SQL provides a mechanism for importing data from a file in to variables to used called HOST VARIABLES. HOST variables are recognized by the colon (:) ahead of their names. Assume we have a following procedure: CREATE PROCEDURE SUPPORT_PROCS.ASSIGN_VALUE(INOUT name char(20),INOUT salary DECIMAL(8,2) ,OUT ranvalue DECIMAL(10,2)) BEGIN SET ranvalue = salary * random(1,6); END; Here we provide the name and salary in the procedure and we get a random value output which is the multiplication of the salary and a random value. Say we have a file as below with name and salary details for each person and we want to run the procedure for each record in the file. more inputfile1 sukul    100.11 rajan   1200.11 Following is how we code in bteq to get this: .IMPORT REPORT FILE=inputfile1; .REPEAT *; USING (name1 CHAR(5),salary1 CHAR(10)) CALL SUPPORT_PROCS.ASSIGN_VALUE(:name1,:salary1,A); Note the colons used to represent the host variables. REPEAT * indicates that the CALL should be repeated for every record in the file. Note: As are reading all data as characters we need to use .IMPORT REPORT FILE (Other option is .IMPORT DATA FILE=) Following is how the result looks like:    *** Starting Row 0 at Sat Apr 27 14:35:03 2013  *** Procedure has been executed.  *** Total elapsed time was 1 second. name1  salary1         ranvalue -----  ----------  ------------ sukul      100.11        200.22  *** Procedure has been executed.  *** Total elapsed time was 1 second. name1  salary1         ranvalue -----  ----------  ------------ rajan     1200.11       7200.66  *** Warning: Out of data.  *** Finished at input row 2 at Sat Apr 27 14:35:03 2013  *** Total number of statements: 2,  Accepted : 2,  Rejected : 0  *** Total elapsed time was 1 second.  *** Total requests sent to the DBC = 2  *** Successful requests per second = 2.000  BTEQ -- Enter your DBC/SQL request or BTEQ command:

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.

Teradata Stored Procedures - Introduction

• Stored Procedure is a Teradata object defined in database or user space.
   
• Stored Procedure contains two types of statements:
• SQL statements
• SPL statements(Stored Procedure Language)
   

• Stored Procedures need Perm space.
   
• Stored Procedures are compiled, Stored and Executed on the server side.
   
• The Source code can also be stored optionally

• Restrictions on using Stored Procedures:
• Stored Procedure cannot contain DDL and DCL Statements.
• Stored Procedure cannot EXPLAIN or USING statements.

• We can pass parameters to stored procedures. Parameters can be input, output or input/output. The type of parameter is indicated by the words IN, OUT or INOUT.

• IN - Values that are supplied to the stored procedure at execution
• OUT - Values that are returned from the stored procedure to the user at call completion 
• INOUT - Values that are both supplied to and returned from the stored procedure

• Stored Procedure can contain up to 1024 parameters.

• Example:

Creating a simple procedure:

CREATE PROCEDURE

TEST_PROCS.TEST_PROC

( IN var1 INTEGER, OUT var2 CHAR(40)) --> IN indicates the input parameter, OUT indicates the output parameter. Data types should be provided.

BEGIN

IF var1 = 0 THEN

SET var2 = 'THE VALUE ENTERED IS ZERO';  --> SET is used to assign values to parameters or local variables. Here we set values to an output variable.

ELSEIF var1 < 0 THEN

SET var2 = 'THE VALUE IS NEGATIVE'; --> SET also ends with a semicolon

ELSE --> IF statement may have multiple ELSEIF, but only one ELSE

SET var2 = 'THE VALUE IS POSITIVE';

END IF;  --> IF statement must be terminated with END IF

END; -->  Stored Procedure body is enclosed inside BEGIN and END. END should end with a semi colon.

Imp Note: When Using SET to assign values to parameters the Input type parameters(IN) should always be on the right side of = sign (.ie should be the sending fields)  and output (OUT)  type parameters should always be on the left side of =.(.ie should be receiving fields)

If this is not followed we would get an error.

Calling a simple procedure:

CALL TEST_PROCS.TEST_PROC(2,variable2) --> All the parameters must be represented in the CALL statement. Note that the output parameter must be specified using the parameter name(variable2 in the example). The Output Parameter value is reported similar to a column value from a table.

Result:

variable2

THE VALUE IS POSITIVE                  

• Arguments that are passed to the call statement can be defined using constants, variable or both

We can call the same procedure as shown in above example as below:

CALL SUPPORT_PROCS.TEST_PROC(3*5,Column2)

Result:

var2

THE VALUE IS POSITIVE                  

CALL SUPPORT_PROCS.TEST_PROC(-2+1,Column2)

Result:

var2

THE VALUE IS NEGATIVE