ayuda con stored procedure
Publicado por daniel (3 intervenciones) el 24/01/2006 15:48:15
tengo el siguiente procedimiento en mysql,
delimiter //
CREATE PROCEDURE `procedimiento1`(IN_DATOS datetime, IN_DISPOSITION varchar(45), OUT uniqueid_a varchar(32), OUT userfield_b varchar(255), OUT accountcode_c varchar(20), OUT src_d varchar(80), OUT dst_e varchar(80), OUT dcontext_f varchar(80), OUT clid_g varchar(80), OUT channel_h varchar(80), OUT dstchannel_i varchar(80), OUT lastapp_j varchar(80), OUT lastdata_k varchar(80), OUT calldate_l datetime, OUT duration_m int(11), OUT billsec_q int(11), OUT disposition_n varchar(45), OUT amaflags_o int(11))
BEGIN
DECLARE variable1 CHAR(20);
DECLARE variable2 CHAR(20);
IF EXISTS (select * from cdr where calldate like '%IN_DATOS%') THEN
SELECT uniqueid, userfield, accountcode, src, dst, dcontext, clid, channel, dstchannel, lastapp, lastdata, calldate, duration, billsec,disposition, amaflags into uniqueid_a, userfield_b, accountcode_c, src_d, dst_e, dcontext_f, clid_g, channel_h, dstchannel_i, lastapp_j, lastdata_k, calldate_l, duration_m, billsec_q, disposition_n, amaflags_o from cdr where calldate like '%IN_DATOS%';
ELSE
SET variable1 = 'fecha no existe';
END IF;
IF EXISTS (select * from cdr where calldate like '%IN_DISPOSITION%') THEN
SELECT uniqueid, userfield, accountcode, src, dst, dcontext, clid, channel, dstchannel, lastapp, lastdata, calldate, duration, billsec,disposition, amaflags into uniqueid_a, userfield_b, accountcode_c, src_d, dst_e, dcontext_f, clid_g, channel_h, dstchannel_i, lastapp_j, lastdata_k, calldate_l, duration_m, billsec_q, disposition_n, amaflags_o from cdr where calldate like '%IN_DISPOSITION%';
ELSE
SET variable2 = 'parametro de consulta no existe';
END IF;
END
//
solo tengo dos parametros de entrada, pero el problema es que no se como invocar este procediento con call; si alguien sabe por favor hagamelo saber aqui en el foro o en [email protected] o [email protected], es urgente.
delimiter //
CREATE PROCEDURE `procedimiento1`(IN_DATOS datetime, IN_DISPOSITION varchar(45), OUT uniqueid_a varchar(32), OUT userfield_b varchar(255), OUT accountcode_c varchar(20), OUT src_d varchar(80), OUT dst_e varchar(80), OUT dcontext_f varchar(80), OUT clid_g varchar(80), OUT channel_h varchar(80), OUT dstchannel_i varchar(80), OUT lastapp_j varchar(80), OUT lastdata_k varchar(80), OUT calldate_l datetime, OUT duration_m int(11), OUT billsec_q int(11), OUT disposition_n varchar(45), OUT amaflags_o int(11))
BEGIN
DECLARE variable1 CHAR(20);
DECLARE variable2 CHAR(20);
IF EXISTS (select * from cdr where calldate like '%IN_DATOS%') THEN
SELECT uniqueid, userfield, accountcode, src, dst, dcontext, clid, channel, dstchannel, lastapp, lastdata, calldate, duration, billsec,disposition, amaflags into uniqueid_a, userfield_b, accountcode_c, src_d, dst_e, dcontext_f, clid_g, channel_h, dstchannel_i, lastapp_j, lastdata_k, calldate_l, duration_m, billsec_q, disposition_n, amaflags_o from cdr where calldate like '%IN_DATOS%';
ELSE
SET variable1 = 'fecha no existe';
END IF;
IF EXISTS (select * from cdr where calldate like '%IN_DISPOSITION%') THEN
SELECT uniqueid, userfield, accountcode, src, dst, dcontext, clid, channel, dstchannel, lastapp, lastdata, calldate, duration, billsec,disposition, amaflags into uniqueid_a, userfield_b, accountcode_c, src_d, dst_e, dcontext_f, clid_g, channel_h, dstchannel_i, lastapp_j, lastdata_k, calldate_l, duration_m, billsec_q, disposition_n, amaflags_o from cdr where calldate like '%IN_DISPOSITION%';
ELSE
SET variable2 = 'parametro de consulta no existe';
END IF;
END
//
solo tengo dos parametros de entrada, pero el problema es que no se como invocar este procediento con call; si alguien sabe por favor hagamelo saber aqui en el foro o en [email protected] o [email protected], es urgente.
Valora esta pregunta


0