Stored Procedures

General

Stored Procedures

Hi Team I have created a procedure with cursor which will fetch each output from one table and load to another table replace procedure DB.test_proc1() Begin Declare var1 character(1); Declare C1 cursor For Select m from tab1 where m in ('a','b'); Open C1; While(SQLCode=0) DO Fetch C1 into var1; Insert into tab2(m) values(var1); End while; Close c1; End; The loop is keep on executing as the while loop is always positive. What is the way to break the loop after reading once from the cursor value. tab1 contains only one column with values (a,b,c,d,e)
1 REPLY
Teradata Employee

Re: Stored Procedures

As written, you are testing the SQLCODE at the top of the loop, i.e. after the OPEN and after each INSERT. You are never checking the status of the FETCH. There are a number of ways to address this issue, including:

  • Rewrite using a FOR loop instead of explicit OPEN/FETCH/CLOSE
  • Label the WHILE block (e.g. W1: WHILE ...)  and add an IF statement after the FETCH to check the status and exit the loop, e.g. IF SQLCode<>0 THEN LEAVE W1 ENDIF;
  • Do an initial FETCH (and check the status!) prior to the WHILE, with INSERT and subsequent FETCH inside the WHILE

...