Archives of the TeradataForum

Message Posted: Wed, 03 Dec 2003 @ 14:16:13 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

1) This procedure will be created under user that is also the owner for the procedure which will call it.

The user is under dbc.

CREATE procedure User11.ExecuteDynamicSQL(
     IN pi_sqlStr varchar(8000)
        )

BEGIN
CALL dbc.sysexecsql ( :pi_sqlStr ) ;
END;

2) This procedure present in my database will call above procedure. Temptable is just a temporary table used to store the output of select. and then I am querying temptable to get output of select in variable.

CREATE PROCEDURE mydb.proc1 (
IN pi_ReturnedValue INTEGER,
IN pi_Operator       VARCHAR(20),
IN pi_TargetValue   INTEGER,
OUT po_Res           INTEGER
)

BEGIN
DECLARE v_stmt VARCHAR(200);
DECLARE v_retval char(1);

SET po_res = 0;
SET v_stmt = ' Insert into mydb.TEMPTABLE select 1  where ' ||
        pi_ReturnedValue || ' ' || pi_operator || ' ' || pi_TargetValue;

call user1.ExecuteDynamicSql (:v_stmt);

SELECT cast(RetValue as Integer)  INTO :v_retVal  FROM mydb.TEMPTABLE;

DELETE FROM mydb.TEMPTABLE;

if trim(v_RetVal) <> '1' then
 SET po_res = 0;
end if;

END;

Appropriate rights have to be given to each object/user/db . Like . proc1 procedure should have execute permission to user1.executedynamicsql.

Remember, Rights can be assigned at procedure levels . :)

Regards,

Rachana

	<Prev	Next>		<<First	<Prev	Next>	Last>>