MySQL Forums
Forum List  »  Informix

Dynamic SQL in Stored Procedure
Posted by: aftab khan
Date: November 13, 2006 04:59AM

When I insert new row in `cdr` table, I get following error. I am using MYSQL Server version: 5.0.24a-community-nt, and that version support dynamic sql (prepare and execute statments) in Stored Procedures; Can any body help

#1336 - Dynamic SQL is not allowed in stored function or trigger

////////////////////////////////////////////////////////////////

CREATE TRIGGER trigger1 after INSERT ON cdr
FOR EACH ROW
BEGIN

DECLARE id BIGINT;
DECLARE vname VARCHAR(200);
DECLARE destination VARCHAR(200) DEFAULT 'null';
DECLARE rows1 BIGINT DEFAULT 0;
DECLARE sql1 VARCHAR(200);
DECLARE sql2 VARCHAR(200);

SELECT v.vendorid,v.name, ve.DestinationID INTO id, vname, destination
FROM (select name, v.VendorID from vendor v natural join vendorendpoints where endpoint=NEW.Field27) v NATURAL JOIN (SELECT * FROM vendorextraction order by length desc) ve
WHERE
substr(NEW.Field8 , 1 ,ve.length)=substr(CONCAT(ve.Prefix,ve.OperatorList),1,ve.length) limit 0,1;

set sql1='insert into `'|| CONCAT(vname,id) || '` values('|| id || ',\"' || vname || '\",\"' || NEW.Field5 || '\",\"' || NEW.Field35 || '\",\"' || NEW.Field12 || '\",\"' || NEW.Field8 || '\",\"'|| NEW.Field0 || '\")';
set sql2='insert into unknown values('|| id || ',\"' || vname || '\",\"' || NEW.Field5 || '\",\"' || NEW.Field35 || '\",\"' || NEW.Field12 || '\",\"' || NEW.Field8 || '\",\"'|| NEW.Field0 || '\")';

IF (id >0) THEN
call testing(sql1);
ELSE
call testing(sql2);
END IF;

END

Options: ReplyQuote


Subject
Views
Written By
Posted
Dynamic SQL in Stored Procedure
14173
November 13, 2006 04:59AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.