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: