Stored Procedure

[cosec]

How do I do a stored procedure to do the following

IF #RERUN_IND#='Y' THEN

DELETE FROM TBL_A WHERE TBL_A.MONTH=#MONTH_IND#;

ELSE 0


Pls Advice

Thanks

[sachin1]

hello,

you have to create a procedure in database having input parameters that you require for your processing.

Next in Stored_Procedure stage goto --> syntax tab -->give the name for procedure --> uncheck generate procedure call ---> then call your procedure like for example

BEGIN p2('#val#'); END;

#val# is the input parameter for job which could be directly used as a value in your procedure.

[cosec]

Hello sachin,
Thanks but I am using the server edition and I believe the stored_proc is not available....

so can I use the statement I mentioned earlier in a routine ? is the sysntax correct ?

hello,

you have to create a procedure in database having input parameters that you require for your processing.

Next in Stored_Procedure stage goto --> syntax tab -->give the name for procedure --> uncheck generate procedure call ---> then call your procedure like for example

BEGIN p2('#val#'); END;

#val# is the input parameter for job which could be directly used as a value in your procedure.

[JoshGeorge]

The ODBC stage has stored procedure as one of its options in server edition.

Thanks but I am using the server edition and I believe the stored_proc is not available....

[cosec]

Ok thanks ...but can I also use this SQL statement in a routine ?

The ODBC stage has stored procedure as one of its options in server edition.

Thanks but I am using the server edition and I believe the stored_proc is not available....

[JoshGeorge]

I reccomend to search the forum. Calling stored procedure/ Executing SQL thru routine has been discussed many times out here.

[sachin1]

i am using Datastage server 7.5 (server edition)... we have a stored procedure stage.

[ray.wurlod]

Why not just create a job to delete the rows? Use user-defined SQL, supply the date (as a job parameter) and refer to this in the WHERE clause of your DELETE statement.

[cosec]

Yes that's possible...but I would like to do it in the same job...

Why not just create a job to delete the rows? Use user-defined SQL, supply the date (as a job parameter) and refer to this in the WHERE clause of your DELETE statement. ...

[JoshGeorge]

Then why not do it in the 'same job'.

[cosec]

Then why not do it in the 'same job'.

Yes I am researching on how to use the stored procedure..

Thank You..

[ray.wurlod]

You DON'T NEED a stored procedure!!! Just use a DELETE command as the user-defined SQL.

[cosec]

You DON'T NEED a stored procedure!!! Just use a DELETE command as the user-defined SQL. ...

I tried the delete command in Output Link 1 and the insert in Output Link 2. But when I do this The table gets locked...


how can i prevent this...? the transaction size was set to 1 in the DB2 stage attached to Output link 1

[ray.wurlod]

By not doing it in the same job, as originally advised.

[chulett]

Or use a single target stage, not two.