All Forums Database
mithunk 7 posts Joined 02/13
19 Feb 2013
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?
 

Mithun
dnoeth 4628 posts Joined 11/04
19 Feb 2013

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

Dieter

mithunk 7 posts Joined 02/13
20 Feb 2013

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

Mithun

dnoeth 4628 posts Joined 11/04
20 Feb 2013

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

Dieter

roj 1 post Joined 06/13
23 Jun 2013

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

Fred 1096 posts Joined 08/04
26 Jun 2013

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

gwizdek 1 post Joined 02/13
12 Sep 2013

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?

vetriselvan 9 posts Joined 04/14
03 Jun 2014

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

itche_scratche 4 posts Joined 03/07
25 Feb 2015

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;
 

Deepakji 5 posts Joined 06/14
26 Aug 2016

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

Glass 225 posts Joined 04/10
27 Aug 2016

Use:
Dynamic Result sets 2, Declare and open a second cursor.
 
Rglass

You must sign in to leave a comment.