Cursors in stored procedures

Database
Enthusiast

Cursors in stored procedures

Hi,

I have to create a stored procedure which returns resultset. Common answer to this was the usage of cursor inside the stored proc. I have few questions on using cursors inside stored procedure.

My first question would be Is there any other means by which we can return a resultset from a teradata stored procedure ?

If at all using cursor is the way, would it affect in terms of performance?

To my knowledge, cursor would fetch rows sequentially.  How does this incorporate parallelism?In simple terms, will the cursor usage perform a single amp operation or an all amp operation?

10 REPLIES
Junior Contributor

Re: Cursors in stored procedures

Hi Mithun,

the syntax for returning result set is based on Standard SQL, but it's not an actual cursor (only those you actually process rows using fetch or FOR are sequential).

A cursor for a DYNAMIC RESULT SET is just like any SELECT answer set, it's simply returned to the client:

REPLACE PROCEDURE proc ()
DYNAMIC RESULT SETS 1
BEGIN
DECLARE c CURSOR WITH RETURN ONLY FOR
SELECT * FROM dbc.TablesV;
OPEN c;
END;

Dieter

Enthusiast

Re: Cursors in stored procedures

Do we have any other option apart from using cursors to return resultset from a teradata stored procedure?

Junior Contributor

Re: Cursors in stored procedures

Hi Mithun,

there's no other option, but it's *not* a cursor, it's just the syntax of a cursor.

Dieter

N/A

Re: Cursors in stored procedures

Hi,

I use cursor in my TD stored procedure. But the output which i get is doubled. Suppose if i just give a select * from tablename without using a cursor I get around 500 rows retrieved. But after using Cursor I get 1000 rows retrieved.

syntax:

replace procedure proc(in aaa timestamp(6)) dynamic result sets 1

declare cursor c1 with return only for select col1,col2,col3 from tablename1;

declare cursor c2 for select * from tablename2;

then i perform some insert update operations

open c1

end;

can someone please help

Teradata Employee

Re: Cursors in stored procedures

You will have to provide more detail if you want assistance. Simply using a cursor will not double the number of rows.

Re: Cursors in stored procedures

Hi,

Suppose I have 3 cursors declared within SP:

REPLACE PROCEDURE "schema"."sproc" ( IN "param" DECIMAL(25, 0))

    DYNAMIC RESULT SETS 3

BEGIN

  DECLARE c1 CURSOR WITH RETURN ONLY FOR

    select x from schema.A where ID = param;

  DECLARE c2 CURSOR WITH RETURN ONLY FOR

    select y from schema.B where ID = param;

  DECLARE c3 CURSOR WITH RETURN ONLY FOR

    select z from schema.C where ID = param;

  OPEN c1;

  OPEN c2;

  OPEN c3;

END;

Are these selects executed in sequence (c1 , then c2, then c3) or parallel?

Is there any difference if I move OPEN statement into declare cursor body, like in second post?

Enthusiast

Re: Cursors in stored procedures

Hi,

I need to parameterize the table name and get the all the column list from dbc table and use it for insert. Is it that possible without using dynamic SQL? Because we cant use select in dynamic SQL so wondering how to achieve this? The logic will be like below :

REPLACE PROCEDURE SAMPLE (

DATABASE1 VARCHAR(30),

DATABASE2 VARCHAR(30),

TABLE1  VARCHAR(30),

TABLE2 VARCHAR(30)

 )

BEGIN

DECLARE COL_VALUES VARCHAR(1500);

DECLARE INS_QRY VARCHAR(2500);

CREATE TABLE COLUMNS_LIST

(

COL_LIST VARCHAR(30)

);

INSERT INTO COLUMNS_LIST

SEL COLUMNNAME FROM DBC.COLUMNS WHERE DATABASENAME = 'DATABASE1' AND TABLENAME = 'TABLE1'

INTERSECT

SEL COLUMNNAME FROM DBC.COLUMNS WHERE DATABASENAME = 'DATABASE2' AND TABLENAME = 'TABLE2' ;

SELECT COL_LIST INTO :COL_VALUES FROM COLUMNS_LIST;

SET INS_QRY =

'INSERT INTO' ||DATABASE1||'.'||TABLE1|| '

(

 :COL_VALUES

 )

 SEL

   :COL_VALUES

  FROM '||DATABASE2||'.'||TABLE2||';';

  EXECUTE IMMEDIATE INS_QRY;

END;

regards

Re: Cursors in stored procedures

Instead of EXECUTE IMMEDIATE INS_QRY;

You prepare from ins_qry below is the example.

BEGIN

  DECLARE C1 CURSOR WITH RETURN ONLY FOR S1;

  PREPARE S1 FROM INS_QRY;

  OPEN C1;

END;

Enthusiast

Re: Cursors in stored procedures

REPLACE PROCEDURE proc ()

DYNAMIC RESULT SETS 1

BEGIN

  DECLARE c CURSOR WITH RETURN ONLY FOR

  SELECT * FROM dbc.TablesV;

  OPEN c;

END;

 

Hi dnoeth,

 

if i want to use one more output in the stored procedure . For eg : "sel date" or some other compatible sel statement

along with "sel * from dbc.tables". Is it possible ?

 

Kindly guide me