Tema: Asignar valor a variable dentro de if
Ver Mensaje Individual
  #17 (permalink)  
Antiguo 07/12/2012, 11:46
jnavarrete2311
 
Fecha de Ingreso: noviembre-2012
Mensajes: 30
Antigüedad: 12 años
Puntos: 0
Respuesta: Asignar valor a variable dentro de if
Pues no consigo hacerlo en workbench ni en phpmyadmin, workbench solo me dice que tengo errores de sintaxis pero no me dice donde y phpmyadmin me marca un error en un insert pero no tengo idea de por que este es el query ya con delimiter en phpmyadmin:

Código MySQL: 
Ver original
  1. Delimiter //
  2. CREATE PROCEDURE sp_InsertCustomer (
  3.     IN Firstname VARCHAR(100), 
  4.     IN Lastname VARCHAR(100), 
  5.     IN Email VARCHAR(100), 
  6.     IN Birthday DATETIME,
  7.     
  8.     IN Company VARCHAR(100), 
  9.     IN Address1 VARCHAR(100), 
  10.     IN Postcode INT, 
  11.     IN City VARCHAR(100), 
  12.     IN Phone INT
  13.     )
  14. BEGIN
  15. DECLARE regs        INT;
  16.     DECLARE ExistsUser   INT;
  17.     DECLARE Customer_ID INT;
  18.     SET regs = 0;
  19.     SELECT COUNT(*) INTO regs
  20.     FROM `ps_customer` 
  21.     WHERE id_customer = 3;
  22.  
  23.     IF regs > 0 THEN
  24.         SET ExistsUser = 1;
  25.     END IF;
  26.  
  27.     IF ExistsUser = 1 THEN
  28.         INSERT INTO  `ps_customer`
  29.             (id_default_group,id_risk,firstname,lastname,email, passwd,birthday, newsletter_date_add,max_payment_days,secure_key,ac tive,date_add,date_upd)
  30.         VALUES(
  31.                3, 0, Firstname, Lastname, Email, '12345',Birthday,'0000-00-00 00:00:00',0,
  32.                '2293b4751c4ff4517bb997eb9c141a76',1,CURRENT_TIMESTAMP(),CURRENT_TIMESTAMP()
  33.               );
  34.  
  35.         SET Customer_ID = LAST_INSERT_ID();
  36.         IF Customer_ID > 0 THEN
  37.             INSERT INTO  `ps_customer_group` (id_customer, id_group)
  38.                 VALUES(Customer_ID, 3);
  39.             INSERT INTO  `ps_guest`(id_operating_system, id_web_browser, id_customer, screen_resolution_x, screen_resolution_y,
  40.  screen_color, sun_java, adobe_flash, adobe_director, apple_quicktime, real_player, windows_media, accept_language)
  41.                 VALUES(3, 10, Customer_ID, 0, 0, 0, 0, 0, 0, 0, 0, 0,'');
  42.             INSERT INTO  `ps_address`(id_country, id_state, id_customer, id_manufacturer, id _supplier, id_warehouse, alias, company, lastname, firstname, address1,
  43.  postcode, city, phone, vat_number, dni, DATE_ADD, date_upd, active, deleted)
  44.                 VALUES(145, 67, Customer_ID, 0, 0, 0, 'Business', Company, Lastname, Firstname, Address1, Postcode, City, Phone, '','' , CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP(), 1, 0);
  45.         END IF;
  46.     END IF;
  47. END
  48. //
-----------------------------------------------------------------------------------------------------------

y este es el error que marca:

Código MySQL: 
Ver original
  1. Error
  2.  
  3. Parece haber un error en su consulta de SQL. La salida generada por el servidor de MySQL, de existir, aparece abajo, en cuyo caso puede ayudar a diagnosticar el problema.
  4.  
  5. ERROR: Signo de puntuación desconocido @ 1947
  6. STR: //
  7. SQL: CREATE PROCEDURE sp_InsertCustomer (
  8.     IN Firstname VARCHAR(100), 
  9.     IN Lastname VARCHAR(100), 
  10.     IN Email VARCHAR(100), 
  11.     IN Birthday DATETIME,
  12.     
  13.     IN Company VARCHAR(100), 
  14.     IN Address1 VARCHAR(100), 
  15.     IN Postcode INT, 
  16.     IN City VARCHAR(100), 
  17.     IN Phone INT
  18.     )
  19. BEGIN
  20. DECLARE regs        INT;
  21.     DECLARE ExistsUser   INT;
  22.     DECLARE Customer_ID INT;
  23.     SET regs = 0;
  24.     SELECT COUNT(*) INTO regs
  25.     FROM `ps_customer` 
  26.     WHERE id_customer = 3;
  27.  
  28.     IF regs > 0 THEN
  29.         SET ExistsUser = 1;
  30.     END IF;
  31.  
  32.     IF ExistsUser = 1 THEN
  33.         INSERT INTO  `ps_customer`
  34.             (id_default_group,id_risk,firstname,lastname,email, passwd,birthday, newsletter_date_add,max_payment_days,secure_key,ac tive,date_add,date_upd)
  35.         VALUES(
  36.                3, 0, Firstname, Lastname, Email, '12345',Birthday,'0000-00-00 00:00:00',0,
  37.                '2293b4751c4ff4517bb997eb9c141a76',1,CURRENT_TIMESTAMP(),CURRENT_TIMESTAMP()
  38.               );
  39.  
  40.         SET Customer_ID = LAST_INSERT_ID();
  41.         IF Customer_ID > 0 THEN
  42.             INSERT INTO  `ps_customer_group` (id_customer, id_group)
  43.                 VALUES(Customer_ID, 3);
  44.             INSERT INTO  `ps_guest`(id_operating_system, id_web_browser, id_customer, screen_resolution_x, screen_resolution_y,
  45.  screen_color, sun_java, adobe_flash, adobe_director, apple_quicktime, real_player, windows_media, accept_language)
  46.                 VALUES(3, 10, Customer_ID, 0, 0, 0, 0, 0, 0, 0, 0, 0,'');
  47.             INSERT INTO  `ps_address`(id_country, id_state, id_customer, id_manufacturer, id _supplier, id_warehouse, alias, company, lastname, firstname, address1,
  48.  postcode, city, phone, vat_number, dni, DATE_ADD, date_upd, active, deleted)
  49.                 VALUES(145, 67, Customer_ID, 0, 0, 0, 'Business', Company, Lastname, Firstname, Address1, Postcode, City, Phone, '','' , CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP(), 1, 0);
  50.         END IF;
  51.     END IF;
  52. END
  53. //
  54.  
  55.  
  56. consulta SQL: Documentación
  57.  
  58. CREATE PROCEDURE sp_InsertCustomer ( IN Firstname VARCHAR(100), IN Lastname VARCHAR(100), IN Email VARCHAR(100), IN Birthday DATETIME, IN Company VARCHAR(100), IN Address1 VARCHAR(100), IN Postcode INT, IN City VARCHAR(100), IN Phone INT ) BEGIN DECLARE regs INT; DECLARE ExistsUser INT; DECLARE Customer_ID INT; SET regs = 0; SELECT COUNT(*) INTO regs FROM `ps_customer` WHERE id_customer = 3; IF regs > 0 THEN SET ExistsUser = 1; END IF; IF ExistsUser = 1 THEN INSERT INTO `ps_customer` (id_default_group,id_risk,firstname,lastname,email, passwd,birthday, newsletter_date_add,max_payment_days,secure_key,ac tive,date_add,date_upd) VALUES( 3, 0, Firstname, Lastname, Email, '12345',Birthday,'0000-00-00 00:00:00',0, '2293b4751c4ff4517bb997eb9c141a76',1,CURRENT_TIMESTAMP(),CURRENT_TIMESTAMP() ); SET Customer_ID = LAST_INSERT_ID(); IF Customer_ID > 0 THEN INSERT INTO `ps_customer_group` (id_customer, id_group) VALUES(Customer_ID, 3); INSERT INTO `ps_guest`(id_operating_system, id_web_browser, id_customer, screen_resolution_x, screen_resolution_y, screen_color, sun_java, adobe_flash, adobe_director, apple_quicktime, real_player, windows_media, accept_language) VALUES(3, 10, Customer_ID, 0, 0, 0, 0, 0, 0, 0, 0, 0,''); INSERT INTO `ps_address`(id_country, id_state, id_customer, id_manufacturer, id _supplier, id_warehouse, alias, company, lastname, firstname, address1, postcode, city, phone, vat_number, dni, DATE_ADD, date_upd, active, deleted) VALUES(145, 67, Customer_ID, 0, 0, 0, 'Business', Company, Lastname, Firstname, Address1, Postcode, City, Phone, '','' , CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP(), 1, 0); END IF; END IF; END //
  59.  
  60. MySQL ha dicho: Documentación
  61. #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'tive,date_add,date_upd)
  62.         VALUES(
  63.                3, 0, Firstname, Lastna' at line 28