[SOLVED] Procedure problem

[windjohn]

New at Procedures --- when I call the procedure, I want to update the parameters into the table, It increments the row, but the other columns are empty. 

 

Problem is I'm not sure how to assign the values to the table

 

CALL test ('2', 'Johnson', 'Tony', '232 Anyplace rd.', 'San Mateo', 'CA', '94404', '2200', '2800', '3200');

 

 

 

delimiter //
CREATE PROCEDURE assignment6 (
	IN p_customer_number int(11),
	IN p_lname varchar(50),
	IN p_fname varchar(50),
	IN p_street varchar(50),
	IN p_city varchar(50),
	IN p_state char(3),
	IN p_zip_code char(10),
	IN p_balance decimal(10,2),
	IN p_credit_limit decimal(10,2),
	IN p_slsrep_number decimal(3,0) 
			       )
BEGIN


	DECLARE lname_var varchar(50);
	DECLARE fname_var varchar(50);
	DECLARE street_var varchar(50);
	DECLARE city_var varchar(50);
	DECLARE state_var char(3);
	DECLARE zip_code_var char (10);
	DECLARE balance_var decimal(10,2);
	DECLARE credit_limit_var decimal(10,2);
	DECLARE slsrep_number_var decimal(3,0); 


	SELECT lname, fname, street, city, state, zip_code, balance, credit_limit, slsrep_number  
	INTO  lname_var,  fname_var, street_var, city_var, state_var, zip_code_var, balance_var, credit_limit_var, 					
	slsrep_number_var FROM customer WHERE customer_number = p_customer_number;

	SET	
		p_lname = lname_var, p_fname = fname_var,p_street = street_var, p_city = city_var,
		p_state = state_var, p_zip_code = zip_code_var,
		p_balance = balance_var, p_credit_limit = credit_limit_var,
		p_slsrep_number = slsrep_number_var;

	set @v_lname = '';
	set @v_fname = '';
	set @v_street =’’;
	set @v_city =’’;
	set @v_state =’’;
	set @v_zip_code =’’;
	set @v_balance =’’;
	set @v_credit_limit =’’;
	set @v_slsrep_number =’’;

IF p_customer_number < 1 or 'NULL' THEN
INSERT INTO customer VALUES
('NULL',@v_lname,
@v_fname,
@v_street,
@v_city,
@v_state,
@v_zip_code,
@v_balance,
@v_credit_limit,
@v_slsrep_number);

ELSE 
UPDATE customer
SET 	lname = @v_lname,
	fname = @v_fname,
	street = @v_street,
	city   = @v_city,
	state  = @v_state,
	zip_code = @v_zip_code,
	balance = @v_balance,
	credit_limit = @v_credit_limit,
	slsrep_number = @v_slsrep_number
	WHERE customer_number = p_customer_number;


END IF;


End
//
delimiter ;

 

Here is the call command

CALL test ('2', 'Johnson', 'Tony', '232 Anyplace rd.', 'San Mateo', 'CA', '94404', '2200', '2800', '3200');

 

Any help appreciated.

 

John

[windjohn]

Problem solved

[fenway]

Problem solved

Please, share your solution.