How do I do this stored procedure correctly?

[riahc3]

I have this:

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `linkacorte`(vid_pedido int(11), vid_producto int(11),vtiempo varchar(50),vid_pastelero int(11),vcorte varchar(200),vnum int(2))
begin
declare campo varchar(20);
DECLARE EXIT HANDLER FOR SQLSTATE '42000'
    SELECT 'Ha introducido un campo incorrecto. Solo se permite 1,2 o 3';

if vnum=1 then
    set campo='programa_corte_1';
elseif vnum=2 then
    set campo='programa_corte_2';
elseif vnum=3 then
    set campo='programa_corte_3';
else
CALL raise_error;
end if;

update pedidos_productos set campo=vcorte where id_pedido=vid_pedido and id_producto=vid_producto and tiempo=vtiempo and id_pastelero=vid_pastelero;
commit;

end

Now I need "campo" to be a column in a row but depending on which number I choose, it is a different one. How can I do this? Thanks.

[riahc3]

Also tried this:

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `linkacorte`(vid_pedido int(11), vid_producto int(11),vtiempo varchar(50),vid_pastelero int(11),vcorte varchar(200),vnum int(2))
begin
declare campo varchar(20);
DECLARE EXIT HANDLER FOR SQLSTATE '42000'
    SELECT 'Ha introducido un campo incorrecto. Solo se permite 1,2 o 3';

if vnum=1 then
    set campo='programa_corte_1';
    SELECT 'Prueba 1';
elseif vnum=2 then
    set campo='programa_corte_2';
    SELECT 'Prueba 2';
elseif vnum=3 then
    set campo='programa_corte_3';
    SELECT 'Prueba 3';
else
CALL raise_error;
end if;
set @sqltext=concat('UPDATE pedidos_productos SET ',@campo,'=vcorte WHERE id_pedido=vid_pedido ANDA id_producto=vid_producto AND tiempo=vtiempo AND id_pastelero=vid_pastelero');
    SELECT 'prepare';
prepare stmt from @sqltext;
    SELECT 'execute';
execute stmt;
deallocate prepare stmt;
#update pedidos_productos set @campo=vcorte where id_pedido=vid_pedido and id_producto=vid_producto and tiempo=vtiempo and #id_pastelero=vid_pastelero;
    SELECT 'commit';
commit;

end

Nothing at all. The select doesnt even display I believe.

[smantscheff]

SELECT in a stored procedure does not display anything. Therefore you cannot use it for debugging.
In your 2nd sample you have a type: ANDA instead of AND.
campo is not the same variable as @campo.

[riahc3]

SELECT in a stored procedure does not display anything. Therefore you cannot use it for debugging.
In your 2nd sample you have a type: ANDA instead of AND.
campo is not the same variable as @campo.

The ANDA is a stupid error. Fixed.

How can I use campo instead of @campo..........?

New:

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `linkacorte`(vid_pedido int(11), vid_producto int(11),vtiempo varchar(50),vid_pastelero int(11),vcorte varchar(200),vnum int(2))
begin
declare campo varchar(20);
DECLARE EXIT HANDLER FOR SQLSTATE '42000'
    SELECT 'Ha introducido un campo incorrecto. Solo se permite 1,2 o 3';

if vnum=1 then
    set campo='programa_corte_1';
    SELECT 'Prueba 1';
elseif vnum=2 then
    set campo='programa_corte_2';
    SELECT 'Prueba 2';
elseif vnum=3 then
    set campo='programa_corte_3';
    SELECT 'Prueba 3';
else
CALL raise_error;
end if;
set @sqltext=concat('UPDATE pedidos_productos SET ',campo,'=vcorte WHERE id_pedido=vid_pedido AND id_producto=vid_producto AND tiempo=vtiempo AND id_pastelero=vid_pastelero');
prepare stmt from @sqltext;
execute stmt;
deallocate prepare stmt;
#update pedidos_productos set @campo=vcorte where id_pedido=vid_pedido and id_producto=vid_producto and tiempo=vtiempo and id_pastelero=vid_pastelero;
commit;

end

It says " Unknown column 'vid_pedido' in 'where clause' ". Any ideas?

[kartisathis]

Hi i think you again did same mistake in concat statement ,
intsead of

SET @sqltext=CONCAT('UPDATE pedidos_productos SET ',campo,'=vcorte WHERE id_pedido=vid_pedido AND id_producto=vid_producto AND tiempo=vtiempo AND id_pastelero=vid_pastelero');

try this

SET @sqltext=CONCAT("UPDATE pedidos_productos SET ",campo,"=vcorte WHERE id_pedido=",vid_pedido," AND id_producto=",vid_producto," AND tiempo=",vtiempo," AND id_pastelero=",vid_pastelero);

and execute the procedure

[riahc3]

Modified it to this and still nothing.......

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `linkacorte`(vid_pedido int(11), vid_producto int(11),vtiempo varchar(50),vid_pastelero int(11),vcorte varchar(200),vnum int(2))
begin
declare campo varchar(20);
DECLARE EXIT HANDLER FOR SQLSTATE '42000'
    SELECT 'Ha introducido un campo incorrecto. Solo se permite 1,2 o 3';

if vnum=1 then
    set campo='programa_corte_1';
    SELECT 'Prueba 1';
elseif vnum=2 then
    set campo='programa_corte_2';
    SELECT 'Prueba 2';
elseif vnum=3 then
    set campo='programa_corte_3';
    SELECT 'Prueba 3';
else
CALL raise_error;
end if;
set @sqltext=concat('UPDATE pedidos_productos SET ',campo,'=',vcorte,' WHERE id_pedido=',vid_pedido,' AND id_producto=',vid_producto,' AND tiempo=',vtiempo,' AND id_pastelero=',vid_pastelero);
prepare stmt from @sqltext;
execute stmt;
deallocate prepare stmt;
#update pedidos_productos set @campo=vcorte where id_pedido=vid_pedido and id_producto=vid_producto and tiempo=vtiempo and id_pastelero=vid_pastelero;
commit;

end

[riahc3]

Noone has any idea what could be happening?

[kartisathis]

Hello sir,
Tell me what your getting finally , if any error means please mention that error man,
and in your procedure you are using SQLSTATE '42000' which means Db access deny error ,
i don`t know for what situation you are using that sql state exception

[riahc3]

Hello sir,
Tell me what your getting finally , if any error means please mention that error man,
and in your procedure you are using SQLSTATE '42000' which means Db access deny error ,
i don`t know for what situation you are using that sql state exception

Im not even getting a error. It just doesnt update.

Im trying this as well:

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `linkacorte`(vid_pedido int(11), vid_producto int(11),vtiempo varchar(50),vid_pastelero int(11),vcorte varchar(200),vnum int(2))
begin
DECLARE EXIT HANDLER FOR SQLSTATE '42000'
    SELECT 'Ha introducido un campo incorrecto. Solo se permite 1,2 o 3';

if vnum=1 then
    set @sqltext=concat('UPDATE pedidos_productos SET programa_corte_1=',vcorte,' WHERE id_pedido=',vid_pedido,' AND id_producto=',vid_producto,' AND tiempo=',vtiempo,' AND id_pastelero=',vid_pastelero);
elseif vnum=2 then
    set @sqltext=concat('UPDATE pedidos_productos SET programa_corte_2=',vcorte,' WHERE id_pedido=',vid_pedido,' AND id_producto=',vid_producto,' AND tiempo=',vtiempo,' AND id_pastelero=',vid_pastelero);

elseif vnum=3 then
     set @sqltext=concat('UPDATE pedidos_productos SET programa_corte_3=',vcorte,' WHERE id_pedido=',vid_pedido,' AND id_producto=',vid_producto,' AND tiempo=',vtiempo,' AND id_pastelero=',vid_pastelero);

else
CALL raise_error;
end if;
prepare stmt from @sqltext;
execute stmt;
deallocate prepare stmt;
#update pedidos_productos set @campo=vcorte where id_pedido=vid_pedido and id_producto=vid_producto and tiempo=vtiempo and id_pastelero=vid_pastelero;
commit;

end

But still nothing....

[riahc3]

BTW, Im not sure what happen to the forums but its kind of weird....

[riahc3]

No one has any idea after the last post?

[smantscheff]

If you want serious help, post a complete test case with table and procedure definitions (CREATE statements), data (INSERT statements), selects and procedure calls, as well as the expected and the actual results.