All Forums Database
lordbwithme2 7 posts Joined 12/08
20 Aug 2009
Teradata stored procedures

Hi all,I have a requirement of passing tables names dynamically to a stored procedure. I have my table names, date fields stored in another table say XYZ. table XYZ--------------------------------------------- From_date | To_date | table_nm3-------------------------------------------- Date1 | date2 | A1Date3 | date4 | A2Now i need to store the count of records in table (say A1) that fall between the Date1 and Date2.insert into temp select count(*) from A1 where A1.dt_column between Date1 and Date2;ORTo put it in simple terms, i need to count the records for all the table names present in XYZ table with their corresponding from_date and to_date and applied.Insert into TEMP select count(*) from TABLE_NM3 Where TABLE_NM3.DT_COLUMN Between From_date and To_date;I tried to use cursors for this. But I am facing a problem when i try to pass the date values from the cursor to the dynamic SQL.Could someone help me on this.??

Raja_KT 1246 posts Joined 07/09
20 Aug 2009

Hi,Lordbwithme2.For your problem, we can have execute , execute immediate. Please let me know the exact syntax once you implemented it.Thanks and regards,Raja

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

lordbwithme2 7 posts Joined 12/08
20 Aug 2009

Hi raja,could u please provide me a sample script that uses "EXECUTE" or "EXECUTE IMMEDIATE".?

lordbwithme2 7 posts Joined 12/08
20 Aug 2009

This is the script that i used./* ***************************************************************** */REPLACE PROCEDURE tempcnt()BEGINDECLARE SQL_STRING VARCHAR(4000); FOR v_cur AS hdcursor CURSOR FOR SELECT * FROM HARIHAX.PREMHEADER ORDER BY run_date DO SET sql_string = 'INSERT INTO temp SELECT ' || V_CUR.SFX || ' as tbl_name, COUNT(*) FROM DB_T_STAG_UNIT_R1_TEST. POLICY_' || v_cur.sfx || ' WHERE PLCY_CHG_EFF_DT BETWEEN ' || v_cur.from_date || ' AND ' || v_cur.to_date; CALL dbc.SysExecSQL(:sql_string); END FOR; END /* ******************************************************************* */i need to make a count of records in the table POLICY_MMYY (where MMYY is the suffix stored in HARIHAX.PREMHEADER table).PREMHEADER table also contains the from_date and to_date as string fields with values in 'YYYY-MM-DD' format. I tried to cast the from_date & to_date to date format and then pass it to the dynamic SQL string (sql_string).but even then it wasnt working..when i ran the above procedure, it was loadin only the suffix values to the TEMP table but not the count.hope i made it clear!!!! :-)

Raja_KT 1246 posts Joined 07/09
21 Aug 2009

Hi,I m sorry I dont have a script.Maybe some onlookers have it .Best of luckRaja

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

sureshbadam 14 posts Joined 09/07
26 Aug 2009

Did this procedure work for you? what type privileges you had to get for the id that used to execute it?

lordbwithme2 7 posts Joined 12/08
01 Sep 2009

Kumar,I was able to compile the proc and run it. But i wasnt getting the desired output as i mentioned in this post.As far as i know, to run dynamic SQL, you need to have a user id for which u hav a certain amount of perm space to create tables/views, macros, procs (i.e USER as a database).Refer this link below to know more details about running dynamic SQL in teradata stored procedure.http://www.teradataforum.com/teradata/20040811_094022.htmThanks,Dinesh Kalidasan

jerboa 10 posts Joined 04/12
22 May 2012

 

Hello everyone!

I can't create stored procedure which will execute (call) other existing stored procedures.

Can somebody help me?

mjj 23 posts Joined 03/10
04 Jun 2012

Hi jerboa,

Please check the execute, execute procedure previleges are granted to the user which you are using.

If the previleges are correct then you can call existing stored procedures.

 

Regards,

You must sign in to leave a comment.