MySQL Forums
Forum List  »  Stored Procedures

Store proc problem
Posted by: Srivats Chandrasekaran
Date: November 15, 2005 02:49AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Store proc problem
2143
November 15, 2005 02:49AM
1582
November 15, 2005 02:53PM
1499
November 21, 2005 12:35AM
1508
November 21, 2005 03:40AM
1578
November 21, 2005 04:48AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.