Types of Stored Procedures
BTEQ supports two types of stored procedures:
An external stored procedure is defined with a CREATE PROCEDURE or REPLACE PROCEDURE statement, along with EXTERNAL references for source files.
For example:
CREATE PROCEDURE xyz
(IN data1 INTEGER, OUT data2 INTEGER)
LANGUAGE CPP NO SQL PARAMETER STYLE TD_GENERAL
EXTERNAL NAME 'CS!xyz!source.cpp';
The C or C++ files can reside on the server or the client. The server calls for the transfer of client-resident files as needed to satisfy input requirements for the CREATE PROCEDURE or REPLACE PROCEDURE request. Source files must be encoded as ASCII (workstation) or EBCDIC (mainframe), regardless of the current session character set. See SQL External Routine Programming for more information.
The retrieval of client-resident external stored procedure files is handled the same way that User-Defined Function files are handled. See “Specifying Files for User-Defined Functions” on page 62 for more details.
An SQL (internal) stored procedure is created or replaced using the COMPILE command, which takes a single SQL Stored Procedure Language (SPL) file as input. The encoding of this file must match that of the current session character set.
The remainder of this section describes the details of using regular SQL stored procedures.
Command Execution
Specify the following command to create SQL stored procedures on the Teradata server:
.COMPILE FILE = example.spl;
Note: The COMPILE command processes only SQL stored procedures.
As BTEQ processes the COMPILE command, it uses the specified file’s content as the SQL stored procedure source text input. It sends the SQL stored procedure source text to the Teradata server for processing the SQL stored procedure definition.
Creating or Replacing an SQL Stored Procedure
To create an SQL stored procedure, invoke a text editor to create a file named example.spl, and enter the SQL stored procedure source text:
CREATE PROCEDURE samplesp1 ()
BEGIN
/* SPL Statements*/
DECLARE V1 INTEGER;
SET V1 = 100;
END;
The preceding example creates a procedure without parameters.
To replace this with another SQL stored procedure with parameters, create a file named example1.spl, and enter the SQL stored procedure source text:
REPLACE PROCEDURE samplesp1 (IN pAccountNo INTEGER, OUT pAmount DECIMAL (10,2))
BEGIN
/* SPL Statements*/
SELECT : Amount
INTO : pAmount
FROM : Account1 WHERE
aCCTnO = :pAccountNo;
END;
Submit the COMPILE command with this file name.
Executing Stored Procedures
Execute stored procedures either interactively, or include them in BTEQ scripts or input stream files.
Use the Teradata SQL CALL statement to execute a stored procedure interactively:
CALL samplesp1 (8888, pAmount);
If the procedure is created with parameters, specify the parameter arguments within the parenthesis.
Including a Stored Procedure in a Script
Use a text editor to include a stored procedure in a BTEQ script or input stream file. To execute the procedure samplesp1 from a script, for example, enter the Teradata SQL CALL statement after the LOGON statement in the script:
.LOGON tdpid/userid, password
CALL samplesp1 (8888, pAmount);
.LOGOFF
Command Execution Messages
Executing the sample SQL stored procedure act, specified in the file example.spl, and containing one parameter, BTEQ produces the following command execution messages in the SYSPRINT file:
.logon mydbs/tdsp01
Password:
*** Logon successfully completed.
*** Transaction Semantics are BTET.
*** Character Set Name is 'ASCII'.
*** Total elapsed time was 1 second.
BTEQ -- Enter your SQL request logon or BTEQ command:
.compile file example.spl
*** Procedure has been created. .
*** Total elapsed time was 5 seconds.
BTEQ -- Enter your SQL request logon or BTEQ command:
call samplesp1 (8888, pAmount);
*** Procedure has been executed.
*** Total elapsed time was 1 second.
pAmount
---------
10000.00
BTEQ -- Enter your SQL request logon or BTEQ command:
sel * from account1;
*** Query completed. One row found. 2 columns returned.
*** Total elapsed time was 1 second.
accountno amount
-------- ---------
8888 10000.00
.LOGOFF
*** You are now logged off from the DBC.
Using the Script File
The following example shows how to create and execute an SQL stored procedure from the script file, when the script is running in ANSI mode. The example.spl file contains the stored procedure source text.
.LOGON e/fml,notebook
.COMPILE FILE = example.spl;
COMMIT;
CALL samplesp1 (8888, pAmount);
.LOGOFF
The next example is a script for a session running in Teradata mode:
.LOGON e/fml,notebook
BT;
.COMPILE FILE = example.spl;
ET;
CALL samplesp1 (pAccountNo, 10000.00);
.LOGOFF