Teradata stored procedures

Database
Enthusiast

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 | A1

Date3 | date4 | A2

Now 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;

OR

To 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.??
8 REPLIES
Enthusiast

Re: Teradata stored procedures

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
Enthusiast

Re: Teradata stored procedures

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

Re: Teradata stored procedures

This is the script that i used.

/* ***************************************************************** */

REPLACE PROCEDURE tempcnt()
BEGIN
DECLARE 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!!!! :-)

Enthusiast

Re: Teradata stored procedures

Hi,

I m sorry I dont have a script.Maybe some onlookers have it .

Best of luck

Raja
Enthusiast

Re: Teradata stored procedures

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

Re: Teradata stored procedures

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.htm

Thanks,
Dinesh Kalidasan
Enthusiast

Re: Teradata stored procedures

Hello everyone!

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

Can somebody help me?

mjj
Teradata Employee

Re: Teradata stored procedures

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,