Use While loop in Stored Procedure in Teradata

Database
Enthusiast

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;

8 REPLIES
Senior Apprentice

Re: Use While loop in Stored Procedure in Teradata

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

Re: Use While loop in Stored Procedure in Teradata

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

Enthusiast

Re: Use While loop in Stored Procedure in Teradata

Use ACTIVITY_COUNT variable.

set v_RowsAffected=ACTIVITY_COUNT;

Vinay

Enthusiast

Re: Use While loop in Stored Procedure in Teradata

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.

Enthusiast

Re: Use While loop in Stored Procedure in Teradata

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

Enthusiast

Re: Use While loop in Stored Procedure in Teradata

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

Enthusiast

Re: Use While loop in Stored Procedure in Teradata

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?

Enthusiast

Re: Use While loop in Stored Procedure in Teradata

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.