Let's talk about Stored Procedures

Blog
The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.

In my opinion stored procedures can be used to enforce standards for commonly used processes and to avoid the same code to be copied out into multiple scripts/programs creating a maintenance nightmare.

But let's list some of the pro's & con's associated with stored procedures.

  • A good way to enforce standards for commonly used processes to avoid same code proliferation.
  • Provide code flexibility.
  • Code resides inside the Teradata environment already compiled avoiding network traffic.
  • Nested stored procedures allowed.
  • UDF utilization allowed.
  • No debug tool available.
  • No automatic error handling.

There are a lot of developers using stored procedures nowadays and it's important to talk about the following topics.

  • Standard error handling
  • How to handle exceptions after nested stored procedures calls?
  • How to use dynamic embedded SQL inside a stored procedure?

Standard Error Handling

 Let's list some important considerations about stored procedure error handling.

  • There is no automatic exception handler inside a stored procedure unless is define.
  • The return code of a stored procedure call is always 0 for a success or an exception execution unless the exception is an access rights error.
  • An outside exception handler process needs to be defined after a stored procedure call to handle an exception.

A stored procedure error handling should have at least 3 basic exception handlers as follows.

  • EXIT HANDLER FOR SQLEXCEPTION
  • CONTINUE HANDLER FOR SQLWARNING
  • CONTINUE OR EXIT HANDLER FOR SQLSTATE = '02000' 

 Exit Handler for SQLEXCEPTION

This exception handler covers any SQLSTATE that is different from '00000‘ (Successful), and from '01000‘ (Warning), and from '02000‘ (Data not found).

The following example shows how to define an exit handler for SQLEXCEPTION and the process writes the exception information into an error log table.

REPLACE PROCEDURE DW.SP_INDEX_STATS 
( IN dbname VARCHAR(30), IN tblname VARCHAR(30),
OUT sqlMsg VARCHAR(133), OUT sqlErrCde INTEGER, OUT sqlStte INTEGER)

BEGIN
DECLARE SQL_ERR_CDE INTEGER;
DECLARE SQL_STATE INTEGER;
DECLARE TMPMSG VARCHAR(133);

L1: BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET SQLERRCDE = SQLCODE;
SET SQLSTTE = SQLSTATE;
SET SQLMSG = TMPMSG;
SET SQL_ERR_CDE = SQLCODE;
SET SQL_STATE = SQLSTATE;
INSERT INTO DW.ERR_STD_PRC_LOG
VALUES ('DW', 'SP_INDEX_STATS' , 'E' , :SQL_ERR_CDE,:SQL_STATE, :TMPMSG, DATE, TIME);
END;

 Continue Handler for SQLWARNING

This exception handler covers SQLSTATE = '01000‘ (Warning).

The following example shows how to define a continue handler for SQLWARNING and the process writes the exception information into an error log table.

      DECLARE CONTINUE HANDLER FOR SQLWARNING
BEGIN
SET SQLERRCDE = SQLCODE;
SET SQLSTTE = SQLSTATE;
SET SQLMSG = TMPMSG;
SET SQL_ERR_CDE = SQLCODE;
SET SQL_STATE = SQLSTATE;
INSERT INTO DW.ERR_STD_PRC_LOG
VALUES ('DW', 'SP_INDEX_STATS' , 'W' , :SQL_ERR_CDE, :SQL_STATE, :TMPMSG, DATE, TIME);
END;

 Continue or Exit Handler for SQLSTATE = '02000‘

This exception handler covers data not found. But in some cases the process to check data not found is defined after every SQL statement instead of an exception handler. This scenario happens when different actions need to be executed depending on the SQL statement issued after a data not found condition is met.

The following example shows how to define a continue or exit handler for SQLSTATE = '02000‘ and the process writes the exception information into an error log table.

      DECLARE CONTINUE HANDLER FOR SQLSTATE = '02000'
BEGIN
SET SQLERRCDE = SQLCODE;
SET SQLSTTE = SQLSTATE;
SET SQLMSG = TMPMSG;
SET SQL_ERR_CDE = SQLCODE;
SET SQL_STATE = SQLSTATE;
INSERT INTO DW.ERR_STD_PRC_LOG
VALUES ('DW', 'SP_INDEX_STATS' , 'W' , :SQL_ERR_CDE, :SQL_STATE, :TMPMSG, DATE, TIME);
END;
OR
DECLARE EXIT HANDLER FOR SQLSTATE = '02000'
BEGIN
SET SQLERRCDE = SQLCODE;
SET SQLSTTE = SQLSTATE;
SET SQLMSG = TMPMSG;
SET SQL_ERR_CDE = SQLCODE;
SET SQL_STATE = SQLSTATE;
INSERT INTO DW.ERR_STD_PRC_LOG
VALUES ('DW', 'SP_INDEX_STATS' , 'E' , :SQL_ERR_CDE, :SQL_STATE, :TMPMSG, DATE, TIME);
END;

 Please notice that this kind of exception handler can be defined for any SQLSTATE.

Error Log Table

 The following example shows how an Error Log table can be defined to hold the information of an exception associated with a stored procedure call.

CREATE MULTISET TABLE DW.ERR_STD_PRC_LOG
(dbase_nam VARCHAR(128) NOT NULL,
std_prc_nam VARCHAR(128) NOT NULL,
err_typ_cde CHAR(1) NOT NULL,
sql_err_cde INTEGER NOT NULL,
sql_state INTEGER NOT NULL,
sql_msg VARCHAR(255) NOT NULL,
dw_crte_dte DATE FORMAT 'YYYY-MM-DD' NOT NULL,
dw_crte_tm INTEGER NOT NULL)
PRIMARY INDEX ERR_STD_PRC_LOG_PI ( dbase_nam ,std_prc_nam )
PARTITION BY RANGE_N(dw_crte_dte BETWEEN
DATE '2009-01-01' AND DATE '2009-12-31' EACH INTERVAL '1' DAY ,
DATE '2010-01-01' AND DATE '2010-12-31' EACH INTERVAL '1' DAY ,
DATE '2011-01-01' AND DATE '2011-12-31' EACH INTERVAL '1' DAY ,
NO RANGE OR UNKNOWN);

This error log table is defined as a PPI table based on the dw_crte_dte (Date) field. But there is also a dw_crte_tm (Time) field defined on the table in case a stored procedure needs to run multiple times on the same day. This error log table can be used for all stored procedures because each entry on the table contains the stored procedure name. This log table can also be used during the debug & testing phases to write the results of every step and process milestones.

Outside Exception Handler

 The following example shows how an outside exception handler can be defined after a stored procedure call.

CALL DW.SP_INDEX_STATS ('DBname', 'TBLname', sqlMsg, sqlErrCde, sqlStte);
.IF ERRORLEVEL > 2 THEN .QUIT 12;
SELECT *
FROM DW.ERR_STD_PRC_LOG
WHERE dbase_nam = 'DW'
AND std_prc_nam = 'SP_INDEX_STATS'
AND err_typ_cde = 'E'
AND dw_crte_dte = DATE;
.IF ACTIVITYCOUNT > 0 THEN .QUIT 12;
.IF ERRORCODE <> 0 THEN .QUIT ERRORCODE;
.QUIT;

 The ERRORLEVEL statement after the stored procedure call will cover any access rights error and the SELECT statement together with the ACTIVITYCOUNT statement will cover any exception error.

How to handle exceptions after nested stored procedure calls?

 An important consideration when using nested stored procedure calls is that an exception process needs to be defined after every nested call to handle the exception. The following example shows how an exception handler process can be defined after a nested stored procedure call.

REPLACE PROCEDURE DW.SP_SMART_STATS
( IN dbname VARCHAR(130), IN tblname VARCHAR(130), OUT sqlMsg VARCHAR(133), OUT sqlErrCde INTEGER, OUT sqlStte INTEGER)

BEGIN
DECLARE SQL_ERR_CDE INTEGER;
DECLARE SQL_STATE INTEGER;
DECLARE tmpMsg VARCHAR(133);
DECLARE WS_TBLKIND CHAR(1) DEFAULT ' ';
DECLARE WS_DB INTEGER DEFAULT 0;

L1: BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET SQLERRCDE = SQLCODE;
SET SQLSTTE = SQLSTATE;
SET SQLMSG = TMPMSG;
SET SQL_ERR_CDE = SQLCODE;
SET SQL_STATE = SQLSTATE;
INSERT INTO DW.ERR_STD_PRC_LOG
VALUES ('DW','SP_SMART_STATS','E',:SQL_ERR_CDE,:SQL_STATE,:TMPMSG,DATE,TIME);
END;

DECLARE CONTINUE HANDLER FOR SQLWARNING
BEGIN
SET SQLERRCDE = SQLCODE;
SET SQLSTTE = SQLSTATE;
SET SQLMSG = TMPMSG;
SET SQL_ERR_CDE = SQLCODE;
SET SQL_STATE = SQLSTATE;
INSERT INTO DW.ERR_STD_PRC_LOG
VALUES ('DW','SP_SMART_STATS','W',:SQL_ERR_CDE,:SQL_STATE,:TMPMSG,DATE,TIME);
END;

DECLARE EXIT HANDLER FOR SQLSTATE = '02000'
BEGIN
SET SQLERRCDE = SQLCODE;
SET SQLSTTE = SQLSTATE;
SET SQLMSG = TMPMSG;
SET SQL_ERR_CDE = SQLCODE;
SET SQL_STATE = SQLSTATE;
INSERT INTO DW.ERR_STD_PRC_LOG
VALUES ('DW','SP_SMART_STATS','E',:SQL_ERR_CDE,:SQL_STATE,:TMPMSG,DATE,TIME);
END;

SET SQLERRCDE = 0;
SET SQLSTTE = 0;

/* Validate if the databasename exists on DBC.DBASE. If not, end with error message. */
SET tmpMsg = 'SELECT 1 FROM DBC.DBASE ...';
SET WS_DB = 0;

LOCK ROW FOR ACCESS
SELECT 1
INTO WS_DB
FROM DBC.DBASE
WHERE DATABASENAME = :dbname ;

/* Validate if the tablename exists on DBC.TABLES and if it is a table or join index. If not, end with error message. */
SET tmpMsg = 'SELECT tablekind FROM DBC.TABLES ...';
SET WS_TBLKIND = ' ';

LOCK ROW FOR ACCESS
SELECT tablekind
INTO WS_TBLKIND
FROM DBC.TABLES
WHERE DATABASENAME = :dbname
AND TABLENAME = :tblname
AND TABLEKIND in ( 'T' , 'I') ;

SET TMPMSG ='CALL DW.SP_INDEX_STATS (:dbname, :tblname, Sqlmsg, sqlerrcde, sqlstte)';

CALL DW.SP_INDEX_STATS (:dbname, :tblname, Sqlmsg, sqlerrcde, sqlstte) ;

SELECT sql_err_cde, sql_state, sql_msg
INTO SQL_ERR_CDE, SQL_STATE, TMPMSG
FROM DW.ERR_STD_PRC_LOG
WHERE dbase_nam = 'DW'
AND std_prc_nam = 'SP_INDEX_STATS'
AND err_typ_cde = 'E'
AND dw_crte_dte = DATE;

IF SQLSTATE = '00000' THEN
SET SQLMSG = TMPMSG;
SET SQLERRCDE = SQL_ERR_CDE;
SET SQLSTTE = SQL_STATE;
INSERT INTO DW.ERR_STD_PRC_LOG
VALUES ('DW', 'SP_INDEX_STATS', 'E', :SQL_ERR_CDE, :SQL_STATE, :TMPMSG, DATE, TIME);
LEAVE L1;
END IF;
...

 The error log table needs to be checked after every nested call to determine if an exception was generated. If so, the exception needs to be passed to the outer stored procedure error handler by writing the exception in the error log table. After that the outside exception handler process will be able to check for the error associated with the inner stored procedure exception.

 How to use dynamic embedded SQL inside a stored procedure?

It's a very useful resource for situations when a SQL needs to be generated at run time. But proper access rights needs to be in place for the stored procedure to be able to call DBC.SYSEXECSQL procedure. The process will run only one SQL at a time. But a multi-statement request can be specified as follows.

    BEGIN REQUEST
CALL DBC.SYSEXECSQL (:statmnt1 || :statmnt2);
END REQUEST;

The size of the SQL statement can not exceed 32000 characters. The following example shows how dynamic embedded SQL can be used inside a stored procedure.

REPLACE PROCEDURE DW.SP_DEL_PPI 
( OUT sqlMsg VARCHAR(133), OUT sqlErrCde INTEGER, OUT sqlStte INTEGER)

-- Main body of Procedure logic/code
BEGIN
DECLARE SQL_ERR_CDE INTEGER;
DECLARE SQL_STATE INTEGER;
DECLARE tmpMsg VARCHAR(133);
DECLARE cntr INTEGER;
DECLARE ws_cntr INTEGER;
DECLARE statmnt VARCHAR(250);

L1: BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET SQLERRCDE = SQLCODE;
SET SQLSTTE = SQLSTATE;
SET SQLMSG = TMPMSG;
SET SQL_ERR_CDE = SQLCODE;
SET SQL_STATE = SQLSTATE;
INSERT INTO DW.ERR_STD_PRC_LOG
VALUES ('DW','SP_DEL_PPI','E',:SQL_ERR_CDE,:SQL_STATE,:TMPMSG,DATE,TIME);
END;

-- WILL BE USED TO TAKE CARE OF THE WARNINGS, MAY USE AN ERROR TABLE.
DECLARE CONTINUE HANDLER FOR SQLWARNING
BEGIN
SET SQLERRCDE = SQLCODE;
SET SQLSTTE = SQLSTATE;
SET SQLMSG = TMPMSG;
SET SQL_ERR_CDE = SQLCODE;
SET SQL_STATE = SQLSTATE;
INSERT INTO DW.ERR_STD_PRC_LOG
VALUES ('DW','SP_DEL_PPI','W',:SQL_ERR_CDE,:SQL_STATE,:TMPMSG,DATE,TIME);
END;

SET SQLERRCDE = 0;
SET SQLSTTE = 0;

SET TMPMSG = 'DELETE FROM DW.ERR_STD_PRC_LOG...';
DELETE FROM DW.ERR_STD_PRC_LOG
WHERE dbase_nam = 'DW'
AND std_prc_nam = 'SP_DEL_PPI'
AND dw_crte_dte = DATE ;

-- CHECK HOW MANY LEVELS NEED TO BE CONCATENATED
SET tmpMsg = 'SELECT COUNT(*) FROM WORKDB.GT_DEL_PPI_STATEMENT ...';
LOCK ROW FOR ACCESS
SELECT COUNT(*)
INTO ws_cntr
FROM WORKDB.GT_DEL_PPI_STATEMENT ;

IF SQLSTATE = '02000' THEN
SET TMPMSG='APP ERROR: FAILED SELECT COUNT(*) FROM WORKDB.GT_DEL_PPI_STATEMENT';
SET SQLMSG = TMPMSG;
SET SQLERRCDE = SQLCODE;
SET SQLSTTE = SQLSTATE;
LEAVE L1;
END IF;

IF ws_cntr > 0 THEN
SET cntr = 1;
ELSE
SET tmpMsg = 'APP WARNING: NO INV DEL STATEMENTS FOUND ON GT_DEL_PPI_STATEMENT';
SET SQLMSG = TMPMSG;
SET SQLERRCDE = SQLCODE;
SET SQLSTTE = SQLSTATE;
LEAVE L1;
END IF;

LPDEL: WHILE cntr <= ws_cntr
DO
SET tmpMsg = 'SELECT SQL_TEXT FROM WORKDB.GT_DEL_PPI_STATEMENT...';
LOCK ROW FOR ACCESS
SELECT SQL_TEXT
INTO statmnt
FROM WORKDB.GT_DEL_PPI_STATEMENT
QUALIFY RANK(SQL_TEXT) = cntr ;

IF SQLSTATE = '02000' THEN
SET tmpMsg ='APP ERROR: FAILED SELECT SQL_TEXT FROM WORKDB.GT_DEL_PPI_STATEMENT...';
SET SQLMSG = TMPMSG;
SET SQLERRCDE = SQLCODE;
SET SQLSTTE = SQLSTATE;
LEAVE L1;
END IF;

IF statmnt <> ' ' THEN
SET tmpMsg = 'CALL DBC.SYSEXECSQL('||statmnt||');...';

CALL DBC.SYSEXECSQL (:statmnt);

IF SQLSTATE <> '00000' AND SQLSTATE <> '02000' THEN
SET tmpMsg ='APP ERROR: FAILED CALL DBC.SYSEXECSQL ('||statmnt||');...';
SET SQLMSG = TMPMSG;
SET SQLERRCDE = SQLCODE;
SET SQLSTTE = SQLSTATE;
SET SQL_STATE = SQLSTATE;
INSERT INTO DW.ERR_STD_PRC_LOG
VALUES ('DW','SP_DEL_PPI','E',:SQL_ERR_CDE,:SQL_STATE,:TMPMSG,DATE,TIME);
LEAVE L1;
END IF;
ELSE
IF SQLSTATE = '02000' THEN
SET tmpMsg = 'APP WARNING: NO RECORDS DELETED'||statmnt||'...';
SET SQLMSG = TMPMSG;
SET SQLERRCDE = SQLCODE;
SET SQLSTTE = SQLSTATE;
SET SQL_ERR_CDE = SQLCODE;
SET SQL_STATE = SQLSTATE;
INSERT INTO DW.ERR_STD_PRC_LOG
VALUES ('DW','SP_DEL_PPI','W',:SQL_ERR_CDE,:SQL_STATE,:TMPMSG,DATE,TIME);
END IF;
END IF;
SET cntr = cntr + 1;
END WHILE LPDEL ;
SET sqlMsg = 'COMPLETED SUCCESSFULLY.';
END L1;
END;

 This stored procedure is being used to delete partition records from PPI tables. The delete statements are generated at run time and inserted into a global temporary table GT_DEL_PPI_STATEMENT. The process reads the delete statements from the global temporary table and call DBC.SYSEXECSQL procedure to execute one at time. Please notice that there is no continue or exit handler for SQLSTATE = '02000' defined and this condition is checked after the SQL statements. Please also notice that the process checks the SQLSTATE after the DBC.SYSEXECSQL call to determine if an exception was generated.

28 Comments
Enthusiast
thanks for the interesting article. I have found these type of samples very useful for development activities. I have a side question, it seems that you are an expert on Store procs, are you familiar with External store procedure in JAVA? I started trying to deploy the java store procedure on TD13 express for proof of concept. However, I kept getting errors ..

Tried both eclipse and manual process. both throw the same error. Here are the steps that I did..

public class jxsp1{
public static void myproc(int[] parm){
parms[0] +=1;
}
}

javac jxsp1.java
jar -cf jar1.jar jxsp1.class

bteq

DATABASE DB1;
CALL SQLJ.INSTALL_JAR('CJ!jar1.jar','jar1','0');

*** procedure has been executed
*** warning: 5241 check output for possible warning encountered in installaing or replacing a jar
*** total elapsed time was 1 second
Check output for possible warnings.
------------------------------------------------------ -------------------------
0 Wed April 28 14:40:20 PDT 2010 META-INF/
71 Wed April 28 14:40:20 PDT 2010 META-INF/MANIFEST.MF
251 Wed April 28 14:40:20 PDT 2010 jxsp1.class

BETQ -Enter your DBC/SQL request or BTEQ command:
CREATE PROCEDURE DB1.myjxsp1
(INOUT R INTEGER)
LANGUAGE JAVA NO SQL
PARAMAETER STYLE JAVA
EXTERNAL NAME 'jar1:jxsp1.myproc';

**** Failure 7980 A JAVA method in the specified Jar which matches that the EXTERNNAL NAME clause was not found /etc/opt/teradata/tdconfig/jarlib/tdbs_1018/ jarlib_1018_1979_1.jar
Teradata Employee
@emilwu: please post this in the Extensibility forum.
Enthusiast
thanks .. already did.. anxiously waiting someone reply...
Sorry but I don't have experience with External stored procedures in JAVA.
Enthusiast
Lot of learning from your blog !!
Would like to request you to touch the sensitive area of SPs inablities in utilising Teradata parallelism. Has things changed with TD 12 ..TD 13?
Do you recommand using them without any fuss?
The parallelism of a SP really depends in terms of how the it is designed/implemented. For example, I designed a SP for Change Data Capture (CDC) to process all records from a staging table in parallel and I also designed a SP to delete partitions from a PPI table one partition at time. So, the bottom it really depends how you code a SP. There changes in TD12 for you to be able to create External SP and I have not checked the changes in TD13.
could you give me source of SP_INDEX_STATS .I studying the TD.
And teach me where(BTEQ etc) can I run Outside Exception Handler sample.

Sorry but I can't provide you the source of SP_INDEX_STATS. The outside exception handler above shows how to check the error log table after the SP call. This is the step that you suppose to do after every SP call.
I'm a Stored Procedure bigot....I love them and don't understand why there seems to be such resistance to them in the field; at least in my experience. What I'd like to see is a discussion regarding the myths about stored procedures (they're incredibly slow, they violate security standards, they can't do what you want...etc.). Maybe I'm wrong, but all those reported objections don't seem to be founded on fact. I'm looking to be educated.
Enthusiast
Dave, unlike other RDBMS, Teradata's strength is in parallel processing. Unfortunately, store procedure, like any other program, is sequential in nature. Most importantly the way the store procedure get executed stays in PE(correct me if I were wrong, other TD experts). that means each step in sp will involves in going back to a single PE and find the execution plan and go after AMPS to execute it.
I believe your experience with slow is rooted from using cursor related operation, cursor , a iterator, fetches data in a row-wise fashion, cannot compete with the parallel execution of data fetch like a regular SQL does, therefore, if you plan to use cursor, be aware that it is simply a row by row operation. Large number of rows will definitely be slow.
Violation of secuirty standard : I believe this is a great feature to offer delegation of security by using store procedure and macro rather than a violation of security. A lot of things developed by DBAs to handle security and giving security functionalities to a store procedure's owner without actually granting privileges to the caller. Such delegation makes it possible to deliver solutions by still complying to enterprise level security requirements.
They cannot do what you want: any specific cases or requirements? SP can do a lot of things and of course there are limitations. Sometimes, there is smart/sneak way to get around certain limitation. If you can offer more detail of your requirement, I think we may go into a little deeper into addressing the limitations/extensibility of SP.
Marcio,

Thank you for this excellent article.

I am starting a new assignment next week for a conversion from Microsoft SQL Server to Teradata. The bulk of it is in Stored Procedures. I can definitely use your examples.

Do you know of any coversion utilities, documentation, etc. that will help convert Microsoft SQL to Teradata's? Especially a utility (or docs, notes, etc.) that will convert SQL Server Stored Procedures to Teradata's?

Thanks Marcio.

John
(858) 335-8360 (M)
Teradata Employee
Are there any special requirements to allow Delete/Insert operations using DBC.SysExecSQL when my procedure is compiled on Database A and I want to perform the operations on Database B? With my current permissions I can execute a static delete or insert from my stored procedure, but when I execute through the DBC.SysExecSQL it fails with permissions issues but does not provide any detail. The procedure successfully uses DBC.SysExecSQL to execute a Select from Database B. The customer is on TD 12.0.3.33 i believe cannot remember the final point release off the top of my head. Thank You for any help you can provide.
the SP needs to be defined under an User instead of a database for you to be able to use the dynamic SQL (DBC.SysExecSQL).
Hello I would like to know information:
Had the error code and the message retrieved from the table
DBC.errormsgs how do I need to replace the variable% vstr?

Example for an error code 3811
3811 Column '% vstr' is NOT NULL. Give the column a value.
Is it possible to THROW an exception within a stored procedure?
I was able to catch an exception when calling a stored procedure from a Java program, but some of the stored procedures that I am converting from Sybase return error codes, and it would be perfect if we could throw an exception rather than returning an error code to the calling program.
Teradata Employee
A SQL Java XSP can throw a Java.lang.SQLException with an exception number and message
that is returned to the database and used to set the SQL state and error message.
The Java standard defines how SQL State values are generated in relation to how the
exception is thrown. If the exception is not a class Java.sql.SQLException then the returned
SQL state must be 38000. If the SQL state isn’t set to 38xxx where xx is user definable then a
39001 is generated.
Enthusiast
Wonderful Article, Marcio. Is there any way to execute the dynamic sql outside of the stored procedure to avoid the access issue with DBC.SysExecSQL?

I have built a dynamic sql inside stored procedure and return it using the out parameter. If we could execute the sql from BTEQ, I believe we could have a better execution strategy. Thanks.
Hi All,

I am asking a basic question ,It would be very help ful for me in understanding SP execution if u let me know .

How the SP is executed/processed by Teradata. What is the execution flow of SP in teradata.
Teradata Employee
Hi all,

I am using a procedure to do some DMLs. I am using DBC.SysExecSQL to execute dynamic DMLs inside the procedure. I have used a HANDLER FOR SQLException and logging all the exception statement in a log table. But when i encounter an error, while doing a merge, "5747 - Target row updated by multiple source rows.", exception handler does not log this error in the log table and find this error on the status bar.

Kindly provide me a solution in this regard.

Thanks in advance
Enthusiast
I have two cursors in one stored procedure.
This code snippet sits just above the first curor declarations:
DECLARE CONTINUE HANDLER FOR SQLSTATE VALUE '42000'

BEGIN
set vErrorResultNbr = 1;
END;

I get this error when I install the sproc:
SPL1027:E(L444), Missing/Invalid SQL statement'E(3707):Syntax error, expected something like a 'CONTINUE' keyword or an 'EXIT' keyword between the 'DECLARE' keyword and the word 'GetUpdResultsCursor'.'.

Any suggestions. I am only setting up for a successful install. I expect further changes needed before it goes to production.
Enthusiast
Hi mtmoura,

Really its a good article. I am new to StoreProcedures in Teradata. Just from few days i have started practising, please help me in answering few doubts.

1.I thought of creating a table in storeprocedure. Before creating i would like to check whether that table existing or not and depending on that state i want to create a table.
in BTEQ we use like If ERRORCODE=0 .goto label like that...
But in SP how we need to achieve this?

2.I tried to read the variable from CASE statement as below but ended with syntax error

CREATE PROCEDURE CASE_PROC
(IN VAR1 BYTEINT, IN VAR2 BYTEINT, OUT MSG CHAR(20))
BEGIN
SET MSG= CASE WHEN VAR1=VAR2 THEN 'THEY ARE EQUAL'

CASE WHEN VAR1 ELSE 'VARIABLE 1 GREATER'
END CASE
END CASE;
END;

Like this i am having many more doubts. If you give your personal mail id i will be in touch to clarify my doubts. Thank you very much for your help.

If any one want to give me guidance please give me your mail id's i will post my questions. Thank you very much for this forum and all the people in the group.
Thanks a ton....really imp info ...
Enthusiast

Hi,

I have a question... is row level locking allowed in dynamic query in teradata stored procedure? I have a test code 

REPLACE PROCEDURE TESTDB.TEST

()

MAIN:

BEGIN

 

    DECLARE vSQL_Text                  VARCHAR(5000);

    

    SET  vSQL_Text =  'LOCKING ROW FOR WRITE SELECT COL1 FROM  TESTDB.tABLE WHERE col1= 200;';

/* Exec Dynamic SQL */

  CALL DBC.SysExecSQL(:vSQL_Text);

 

END

MAIN;

 

CALL   TESTDB.TEST();

 

 

and if i try to execute it then "Error 5568:the sql statement is not supported within a stored procedure" is produced.

 

How can row level locking be achieved in a stored procedure if the query is dynamically generated?

Can GET DIAGNOSTICS be utilized?

N/A

how can we implement  orcale package in teradata??

Enthusiast

Hi, I am new to stored procedures. I have understood the concept of stored procedure. But I am not able to understand that where to code the stored procedure. If I code it in SQL assistance it is giving me syntax error in creates statement itself. Same is the case with BTEQ utility. Please help me out. Thanks in advance.

Enthusiast

I dont think anybody can help with such request. Better to read the manuals and learn to resolve syntax error.

Hi,

I have a stored proc where I explicit trap a particular error, i.e invalid Emp Name. When this error occurs I set the MESSAGE_TEXT and SQLSTATE to specific values.

The EXCEPTION EXIT HANDLER then handles this error and displays the SQLSTATE & MESSAGE_TEXT to the caller of the Proc ==> "CALL Failed. 7693:  CREATE_USER_SL:SQLSTATE U0005: Invalid Employee Name provided".  This is all good.

BUT, I also insert a row into a Log table as part of the EXIT HANDLER, and for some reason, the value that gets inserted into the Log table has extra characters appended at the end,

i.e. "Invalid Employee Name providedee Name provided" instead of "Invalid Employee Name provided".

If I hardcode the message within the Insert into Log_Table statement it works, but I don't want to do that. I don't understand why the extra charaters are being appended to the end.

I have also tried referencing MESSAGE_TEXT in the Insert statement, but that does the same thing.

Any assistance would be great. Thanks.

Format:HTML Format Version:1.0 StartHTML: 165 EndHTML: 11785 StartFragment: 314 EndFragment: 11753 StartSelection: 314 EndSelection: 314


REPLACE PROCEDURE Create_User
(
p_UserName CHAR(7)
,
p_Password VARCHAR(30)
,
p_EmpName VARCHAR(70) /* First Name then Surname */
,
p_EmailAddr VARCHAR(100)
)

BEGIN

DECLARE vSQLSTRING VARCHAR(300);
DECLARE vSQL_ERR_CDE VARCHAR(300);
DECLARE vSQL_STATE CHAR(5);
DECLARE vSQL_ERR_MSG VARCHAR(300);
DECLARE vSQL_WARNING_MSG VARCHAR(300);

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET vSQL_ERR_CDE = SQLCODE;
SET vSQL_STATE = SQLSTATE;
SET vSQL_ERR_MSG = '';
GET DIAGNOSTICS EXCEPTION 1 vSQL_ERR_MSG = MESSAGE_TEXT;

INSERT INTO Log_Table
VALUES (p_UserName, 'SQLEXCEPTION' , :SQLCODE, :SQLSTATE, vSQL_ERR_MSG, CURRENT_TIMESTAMP);
RESIGNAL; -- Send message back to caller of Proc
END;

/* Initialise variables */

SET vSQLSTRING = '';
SET vSQL_ERR_CDE = '';
SET vSQL_STATE = '';
SET vSQL_ERR_MSG='';
SET vSQL_WARNING_MSG='';

/* ----------------------------------- Perform Error Checking --------------------------------------------------------------------------------*/

IF p_EmpName = ''
THEN
SIGNAL SQLSTATE VALUE 'U0005' SET MESSAGE_TEXT = 'Invalid Employee Name provided';
END IF;

END;