Archives of the TeradataForum

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

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

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>>