1,105,625 Community Members

How do I do this stored procedure correctly?

Member Avatar
riahc3
 
1,404 posts since May 2008
Reputation Points: 45 [?]
Q&As Helped to Solve: 14 [?]
Skill Endorsements: 18 [?]
Team Colleague
 
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.

Member Avatar
riahc3
 
1,404 posts since May 2008
Reputation Points: 45 [?]
Q&As Helped to Solve: 14 [?]
Skill Endorsements: 18 [?]
Team Colleague
 
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.

Member Avatar
smantscheff
Nearly a Posting Virtuoso
1,289 posts since Oct 2010
Reputation Points: 265 [?]
Q&As Helped to Solve: 271 [?]
Skill Endorsements: 8 [?]
 
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.

Member Avatar
riahc3
 
1,404 posts since May 2008
Reputation Points: 45 [?]
Q&As Helped to Solve: 14 [?]
Skill Endorsements: 18 [?]
Team Colleague
 
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?

Member Avatar
kartisathis
Newbie Poster
23 posts since Jun 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
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

Member Avatar
riahc3
 
1,404 posts since May 2008
Reputation Points: 45 [?]
Q&As Helped to Solve: 14 [?]
Skill Endorsements: 18 [?]
Team Colleague
 
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
Member Avatar
riahc3
 
1,404 posts since May 2008
Reputation Points: 45 [?]
Q&As Helped to Solve: 14 [?]
Skill Endorsements: 18 [?]
Team Colleague
 
0
 

Noone has any idea what could be happening?

Member Avatar
kartisathis
Newbie Poster
23 posts since Jun 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
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

Member Avatar
riahc3
 
1,404 posts since May 2008
Reputation Points: 45 [?]
Q&As Helped to Solve: 14 [?]
Skill Endorsements: 18 [?]
Team Colleague
 
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....

Member Avatar
riahc3
 
1,404 posts since May 2008
Reputation Points: 45 [?]
Q&As Helped to Solve: 14 [?]
Skill Endorsements: 18 [?]
Team Colleague
 
0
 

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

Member Avatar
riahc3
 
1,404 posts since May 2008
Reputation Points: 45 [?]
Q&As Helped to Solve: 14 [?]
Skill Endorsements: 18 [?]
Team Colleague
 
0
 

No one has any idea after the last post?

Member Avatar
smantscheff
Nearly a Posting Virtuoso
1,289 posts since Oct 2010
Reputation Points: 265 [?]
Q&As Helped to Solve: 271 [?]
Skill Endorsements: 8 [?]
 
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 three months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article