CREATE STORED PROCEDURE EXAMPLE IN TERADATA

Stored Procedures in Teradata are very useful powerful objects especially with every new Teradata versions released. With so many options available for condition handling and dynamic sql, Teradata Stored Procedures makes a SQL Developer life lot better.
Let us take an example to see how we can create a new Stored Procedure in Teradata. In the below example, we will create a stored procedure which will insert few records in some table. The idea is to become familiar with the Stored Procedure syntax. So let's create a table in which we will insert rows within a stored procedure.

CREATE MULTISET VOLATILE TABLE US_STATES , NO JOURNAL, NO LOG,NO FALLBACK
 (
 STATE_NAME VARCHAR(13),
 STATE_ABBR CHAR(2),
 STATE_CAPITAL VARCHAR(13),
 STATE_CITY VARCHAR(13)
 )
 PRIMARY INDEX(STATE_NAME)
 ON COMMIT PRESERVE ROWS
 ;

Now let's create a Stored Procedure which will insert some rows into the table.

CREATE PROCEDURE US_STATE_1 ()
 BEGIN
 INSERT INTO US_STATES VALUES('Alabama','AL','Montgomery','Birmingham');
 INSERT INTO US_STATES VALUES('Alaska','AK','Juneau','Anchorage');
 INSERT INTO US_STATES VALUES('Arizona','AZ','Phoenix','Phoenix');
 INSERT INTO US_STATES VALUES('Arkansas','AR','LittleRock','LittleRock');
 INSERT INTO US_STATES VALUES('California','CA','Sacramento','LosAngeles');
 END;

Try creating this proc by running the above Create Procedure definition.
The proc will be successfully completed. So to create a Stored Procedure you need to give CREATE PROCEDURE PROCEDURE_NAME. You can then give input/output parameters too. We will see about parameters in following posts. Then you can give your desired SQL statements with in BEGIN … END clause.

To run the proc: CALL US_STATE_1();

Once done, you can see message : CALL completed.

Now check the data in the table.

SELECT * FROM US_STATES;

If your proc was successfully compiled and successfully completed, then you should be able to see 5 rows in the table.

Kudos, you just created your first proc and executed it successfully.