Variables in a Cursor

Database

Variables in a Cursor

Create procedure ABCD  (in V_WRKTBLNAME_IN varchar(50))

BEGIN

declare v_usage_id integer;

Select evntusage into V_Usage_id;

FOR for_loop_var AS c_primaryfield CURSOR FOR SELECT V_FLDTYPID FROM V_WRKTBLNAME_IN WHERE ETL_ACTN_CD = 'I'

DO

Set of tasks

END FOR ;

END

While compling the stored procedure I am getting the following error

Object 'V_WRKTBLNAME_IN' does not exist.'.

Any help appreciated.

I am getting the value of the table as Input variable . Is it a dynamic sql.. How to handle this in cursor.

Thanks

3 REPLIES
Teradata Employee

Re: Variables in a Cursor

You need to use the Dynamic SQL form of cursor, as documented in the "SQL Stored Procedures and Embedded SQL" manual, something like this:

Create procedure ABCD  (in V_WRKTBLNAME_IN varchar(50))

BEGIN

declare v_usage_id integer;

DECLARE v_stmt VARCHAR(250);

DECLARE c_primaryfield CURSOR FOR prep_stmt;

DECLARE my_fldtypid whatever_data_type_applies;

Select evntusage into V_Usage_id;

SET v_stmt='SELECT V_FLDTYPID FROM '||V_WRKTBLNAME_IN||' WHERE ETL_ACTN_CD=''I''';

PREPARE prep_stmt FROM v_stmt;

OPEN c_primaryfield;

myLoop: LOOP

FETCH c_primaryfield INTO my_FLDTYPID;

IF SQLSTATE <> '00000' THEN LEAVE myLoop;

  Set of tasks

END LOOP myLoop;

close C_primaryfield;

END

Re: Variables in a Cursor

Thanks Fred for you response. My new proc follows your suggestion.

Couple of questions :

1. SET v_stmt='SELECT V_FLDTYPID FROM '||V_WRKTBLNAME_IN||' WHERE ETL_ACTN_CD=''I''';

In this statement I initially did not add double quotes . So I faced some error. whats the reason to use double quote.

2. whether the proc will fail if I dont use the SQLSTATE <> '00000' ?

what will happen if the cursor does not have a single row and  SQLSTATE <> '00000' not mentioned.

3. In TD manuals the example  given is below - How a open cursor can say whether there is a row or not.  IN your above after fetch is sql code = o then there is a row.

OPEN projcursor;

WHILE (SQLCODE=0)

FETCH projcursor INTO var1, var2;

END WHILE;

CLOSE projcursor

Teradata Employee

Re: Variables in a Cursor

  1. Standard SQL syntax; to include a single quote character within a string, enter two in a row. Note that these are all single quote / apostrophe (') characters, not the double quote (") character.
  2. Without some sort of check, the LOOP could continue indefinitely. You could also explicitly DECLARE CONTINUE HANDLER FOR NOT FOUND and add some logic, or check for specific SQLSTATE (e.g. '02000'). My intent was to give a simplified example to replace the FOR in your original post.
  3. OPEN can fail for other reasons, but succeeds whether there is a row or not. OPEN will never return NOT FOUND warning; an un-handled ERROR condition would terminate the procedure. The partial example you cite is intended mainly to illustrate the "INTO" clause. (There are some more relevant examples in the manual, though.)