Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 17 Jan 2002 @ 05:49:51 GMT


     
  <Prev Next>   <<First <Prev
Next>
Last>>
 


Subj:   Re: Arrays in Stored Procedures
 
From:   Mat Ginman

With shell layer I mean that you handle the main logic (e.g. parameter handling) with stored procedures but do the actual data manipulation with SQL statements within a stored procedure. This way you have good error handling (check SPL Exception Handlers from the manual).

E.g.:

REPLACE PROCEDURE main(
  IN  par1 INTEGER,
  IN  par2 INTEGER,
  OUT ret_status INTEGER)
BEGIN
  DECLARE status INTEGER;

-- do some parameter handling

  CALL sub1(:par1,:status);
  IF status = OK THEN
    CALL sub2(:par2,:status);
  END IF;

  SET ret_status = status;
END;

REPLACE PROCEDURE sub1(
  parN INTEGER,
  OUT ret_status INTEGER)
BEGIN
  DECLARE status INTEGER;

  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    SET ret_status = 5;
  END;

  -- Do SQL operation
  UPDATE ..........

  INSERT

  SET ret_status = 0;
END;

You can not return any resultset directly from a stored procedure. You can only return data (a limited amount) through output parameters. You have to stored the data into a table and the use some other tool e.g. BTEQ or FastExport to get resultset to file.

Mats



     
  <Prev Next>   <<First <Prev
Next>
Last>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016