All Forums Database
parthmalhan 26 posts Joined 09/14
16 Sep 2014
Use While loop in Stored Procedure in Teradata

Hi,
I'm new to TeraData. Following is the script i am using to create to While loop in Stored Procedure.
But TeraData Studio is giving me Syntax Error:
can we use loop in Stored Procedure or is there any other way to do that.
Kindly suggest.
CREATE PROCEDURE proc (
        IN i INTEGER,
        IN j INTEGER)
BEGIN
    DECLARE ii integer;
    set ii=i;
    while (ii<j) do
    begin
        set ii=ii+1;
    end while;
END;

dnoeth 4628 posts Joined 11/04
16 Sep 2014

There's an END missing:

CREATE PROCEDURE proc (
        IN i INTEGER,
        IN j INTEGER)
BEGIN
    DECLARE ii integer;
    set ii=i;
    while (ii<j) do
    begin
        set ii=ii+1;
    end;
    end while;
END;

 

Dieter

parthmalhan 26 posts Joined 09/14
16 Sep 2014

thanks for quick reply.
Can you please help me in one more issue.
I want to get no of rows affected from last Query. How can i do that.
for instance :
DECLARE v_RowsAffected integer;
update table set col='a' where <some condition>;
set v_RowsAffected=?  (Count of rows updated by above query)
is there any way to do this.
I'm using following version :
RELEASE,14.00.03.02
VERSION,14.00.03.02

vinaywani 10 posts Joined 11/11
16 Sep 2014

Use ACTIVITY_COUNT variable.
set v_RowsAffected=ACTIVITY_COUNT;
Vinay

parthmalhan 26 posts Joined 09/14
17 Sep 2014

i just got one more issue;
Problem #3:
Can we call dbc.SysExecSQL outside of Stored Procedure.(as a single statement).
i'm getting error "Stored Procedure dbc.SysExecSQL does not exist".
Problen #4:
Im wtiting dynamic stored procedure on runtime in Java, executing that procedure and then drop.
in Procedure i am creating a temp table(every time unique name)
then i join this table with some existing permanent table.
and then need to update the existing table.
the problem is that comiler doesn't let me write this as a static query because temp table doesn't exist, and it gives me error.
i used dbc.SysExecSQL('update statement...'); and it compiled. thats fine.
When i run this procedure, no error and it executed allright.
The problem is, table didnt update.
if i manually copy that command from SysExecSQL it worked fine. But hy its not working there.
 
Can you help me in this.

parthmalhan 26 posts Joined 09/14
17 Sep 2014

hi vijay,
I tried ACTIVITY_COUNT in Procedure, but got error "Cannot resolve column 'ACTIVITY_COUNT'".
I am running this in TeraData Studio (Query Manager) not in BTEQ.
Parth

parthmalhan 26 posts Joined 09/14
17 Sep 2014

Hi Vijay,
I found solution for Affected Rows Count.
Paste this right after your update Query.
GET DIAGNOSTICS t_dups = ROW_COUNT;
 
Here t_dups is the variable name which you can replace with any variable you declared.
 
Parth

Raja_KT 1246 posts Joined 07/09
17 Sep 2014

Not seen your code, but are you executing in ANSI (....commit...)or BTET mode?
Also your temp table is it VTT , where you need on commit preserve rows?

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

shivkumar 6 posts Joined 07/13
06 Oct 2015

Hi,
 
Please help in rectifying below code:
REPLACE PROCEDURE SUPPORTDB.MYSP
(
 OUT MESSAGE VARCHAR(200)
, OUT SQL_OUT VARCHAR(200)
)
DYNAMIC RESULT SETS 1
BEGIN
 DECLARE QUERY VARCHAR(200);
 DECLARE REC_COUNT INTEGER DEFAULT 0;
 DECLARE STATUS CHAR(10) DEFAULT '00000'; 
 DECLARE CNT1 INTEGER;
DECLARE TOTAL INT;
DECLARE DATABASENAME VARCHAR(30);
DECLARE TABLENAME VARCHAR(30);
 DECLARE C1 CURSOR FOR S1;
 DECLARE EXIT HANDLER FOR SQLEXCEPTION,SQLWARNING
 BEGIN
  SET STATUS = SQLCODE;
  SET SQL_OUT = SQLCODE;
  IF(TRIM(STATUS)) = '2652' THEN
   INSERT INTO SUPPORTDB.ERRORLOGTABLE
SELECT CURRENT_DATE,CURRENT_TIME, 'PROC_NAME',:SQLSTATE,:SQLCODE,ERRORTEXT,1
FROM DBC.ERRORMSGS
WHERE ERRORCODE=:SQLCODE;
  ELSE
   INSERT INTO SUPPORTDB.ERRORLOGTABLE
SELECT DISTINCT CURRENT_DATE,CURRENT_TIME, 'PROC_NAME',:SQLSTATE,:SQLCODE,'SUCCESS',1
FROM DBC.ERRORMSGS;

  END IF;
 END;
SET CNT1 = 1;
SELECT MAX(rnk) INTO TOTAL FROM VT_TEST;
 
WHILE CNT1 <= TOTAL
DO
  BEGIN                  
   SELECT databasename,TABLENAME INTO DATABASENAME,TABLENAME FROM vt_test WHERE rnk = cnt1;
   
  SET QUERY = 'SELECT COUNT(*) FROM '||DATABASENAME||'.'||TABLENAME||';';

   PREPARE S1 FROM QUERY;
SET CNT1 = CNT1 + 1; 
OPEN C1;
   FETCH C1 INTO REC_COUNT;
   SET MESSAGE = REC_COUNT;
 
  
  END ;
  END WHILE;
  
END;
My purpose is to execute this procedure for each and every table.
 
Thanks
Shivkumar.

You must sign in to leave a comment.