Creating Stored Procedures - Teradata Database

Teradata Database Administration

Teradata Database
Release Number
English (United States)
Last Update
Product Category

Example 1: Compiling and Executing a Stored Procedure with a Cursor

In most circumstances, it is not optimal to use a cursor for high-volume table updates. For details, see:

However, cursor logic is required to generate a sequence of dynamic SQL statements. This example assumes that there is a file called Sales_Dept_Grant.sql. The file contains this procedure:

REPLACE PROCEDURE Sales_Dept_Grant ( )
Declare GrantStmt VARCHAR(77) Default '';
For UserList as UserList CURSOR FOR
	Select User_Name, Admin_Ind From Sales_Dept_Users
	CASE UserList.Admin_Ind
	WHEN 0 Then
		Set GrantStmt = 'GRANT SELECT on Sales_Dept to ' || UserList.User_Name;
		Set GrantStmt = 'GRANT ALL on Sales_Dept to ' || UserList.User_Name || ' With Grant Option';
	CALL DBC.SysExecSql( :GrantStmt );
END For;

If you are in the directory that contains this file, invoke BTEQ and enter these commands:

.LOGON <IP-addr>/<User-ID>
[Enter the Password]
.COMPILE FILE=Sales_Dept_Grant.sql  /* This compiles the procedure */
CALL Sales_Dept_Grant();            /* This invokes the procedure */

If you are not in the directory that contains the file, then include the path of the file in the compile command:

.COMPILE FILE=C:\Teradata\Procs\Sales_Dept_Grant.sql

Note: To compile the procedure from Teradata SQL Assistant and other .NET, JDBC, or ODBC applications, copy and paste the contents of Sales_Dept_Grant.sql into the command window. Only BTEQ uses the .COMPILE command.

Testing Background

This example was tested using these test tables:

CT Sales_Dept (Acct varchar(44), Sale_Date DATE, Gross DEC(18,2), Net DEC(18,2);
 insert into Sales_Dept values ('Acme Bearings','2013-02-13',12345.00,1234.50)
;insert into Sales_Dept values ('Horse Hockey','2013-03-23',57890.00,5789.00)
;insert into Sales_Dept values ('My Jaxx','2013-04-02',678930.00,67893.00)
CT Sales_Dept_Users (User_Name varchar(32), Admin_Ind BYTEINT);
 insert into Sales_Dept_Users values ('GC151000',1)
;insert into Sales_Dept_Users values ('dulles',0)