Store proc problem
Create table nb_seller_master
(
nb_seller_code Integer NOT NULL AUTO_INCREMENT,
nb_seller_name Char(60),
nb_seller_addrress_1 Char(60),
nb_seller_addrress_2 Char(60),
nb_seller_city Char(20),
nb_seller_state Char(2),
nb_seller_zip Integer,
PRIMARY KEY (nb_seller_code)
)
Store proc
CREATE PROCEDURE nb_seller_info_update
(
IN nb_seller_name CHAR,
IN nb_seller_address_1 CHAR,
IN nb_seller_address_2 CHAR,
IN nb_seller_city CHAR,
IN nb_seller_state CHAR,
IN nb_seller_zip CHAR,
OUT nb_seller_sequence INT,
OUT nb_result CHAR
)
BEGIN
DECLARE MY_ERROR CONDITION FOR SQLSTATE '23000';
DECLARE CONTINUE HANDLER FOR MY_ERROR SET nb_result = 0;
SET nb_result = 0;
SET @nb_seller_name = nb_seller_name ;
SET @nb_seller_address_1 = nb_seller_address_1 ;
SET @nb_seller_address_2 = nb_seller_address_2 ;
SET @nb_seller_city = nb_seller_city ;
SET @nb_seller_state = nb_seller_state ;
SET @nb_seller_zip = nb_seller_zip ;
SELECT nb_seller_code FROM nb_seller_master WHERE nb_seller_name = @nb_seller_name;
SELECT @ROW := FOUND_ROWS();
SET nb_result = @nb_seller_name;
IF @ROW > 0 THEN
SELECT @nb_seller_sequence := nb_seller_code FROM nb_seller_master WHERE nb_seller_name = @nb_seller_name;
ELSE
INSERT INTO nb_seller_master(
nb_seller_name,
nb_seller_addrress_1,
nb_seller_addrress_2,
nb_seller_city,
nb_seller_state,
nb_seller_zip
)
VALUES(
@nb_seller_name,
@nb_seller_address_1,
@nb_seller_address_2,
@nb_seller_city,
@nb_seller_state,
@nb_seller_zip
);
SELECT @nb_seller_sequence := nb_seller_code FROM nb_seller_master WHERE nb_seller_name = @nb_seller_name;
END IF;
SET nb_seller_sequence = @nb_seller_sequence;
END;
Basically if give the seller_name exists, then i should just return the seller_code otherwise it has to update the table with the seller info and return new seller code
IF i give a new seller_name, it is not inserting into the table ( its not coming inside the else part) and the FOUND_ROWS() is showing the number of existing records when it should show zero for a new seller name. If exists( select ) doesnt seem to work