1.11M Members

How do I do this stored procedure correctly?

 
0
 

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.

 
0
 

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.

 
0
 

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.

 
0
 

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?

 
0
 

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

 
0
 

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
 
0
 

Noone has any idea what could be happening?

 
0
 

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

 
0
 

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....

 
0
 

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

 
0
 

No one has any idea after the last post?

 
0
 

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.

You
This article has been dead for over six months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article