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?
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
DECLARE c CURSOR WITH RETURN ONLY FOR
SELECT * FROM dbc.TablesV;
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.
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
can someone please help
Suppose I have 3 cursors declared within SP:
REPLACE PROCEDURE "schema"."sproc" ( IN "param" DECIMAL(25, 0))
DYNAMIC RESULT SETS 3
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;
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?
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 (
DECLARE COL_VALUES VARCHAR(1500);
DECLARE INS_QRY VARCHAR(2500);
CREATE TABLE COLUMNS_LIST
INSERT INTO COLUMNS_LIST
SEL COLUMNNAME FROM DBC.COLUMNS WHERE DATABASENAME = 'DATABASE1' AND TABLENAME = 'TABLE1'
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|| '
EXECUTE IMMEDIATE INS_QRY;
Instead of EXECUTE IMMEDIATE INS_QRY;
You prepare from ins_qry below is the example.
DECLARE C1 CURSOR WITH RETURN ONLY FOR S1;
PREPARE S1 FROM INS_QRY;