CODEWITHSUNDEEP
mysqldatabasestored-procedures
Simon HU
Simon HU

Reputation: 31

MySQL stored procedure returns multiple parameters

I am a noobie to stored procedures. Now I have one that works, but I'd like to learn how to optimize it.

CREATE DEFINER=`simonh`@`%` PROCEDURE `get_normalized_client_id`(IN source_id INT, IN source_division VARCHAR(255), IN source_currency VARCHAR(255), OUT NORMALIZED_ID INT, OUT NAME VARCHAR(255), OUT ADDRESS VARCHAR(255))
BEGIN

IF source_id > 100000 THEN SET source_id = source_id - 100000; END IF;

SELECT
CLIENT.NEW_GROUP_REFERENCE_NUMBER INTO NORMALIZED_ID
FROM ccis_vendors.client_id SOURCE
INNER JOIN ccis_vendors.receivable CLIENT ON CLIENT.id = SOURCE.ACCOUNT_ID
WHERE SOURCE.number = source_id AND SOURCE.division = source_division;

SELECT
CLIENT.name INTO NAME
FROM ccis_vendors.client_id SOURCE
INNER JOIN ccis_vendors.receivable CLIENT ON CLIENT.id = SOURCE.ACCOUNT_ID
WHERE SOURCE.number = source_id AND SOURCE.division = source_division;

SELECT
CONCAT(IFNULL(CLIENT.OPERATION_STREET,''), ' ', IFNULL(CLIENT.OPERATION_CITY,''), ' ', 
IFNULL(CLIENT.OPERATION_STATE_PROVINCE, ''), ' ', IFNULL(CLIENT.OPERATION_ZIP, '')) INTO ADDRESS
FROM ccis_vendors.client_id SOURCE
INNER JOIN ccis_vendors.receivable CLIENT ON CLIENT.id =  SOURCE.ACCOUNT_ID
WHERE SOURCE.number = source_id AND SOURCE.division = source_division;

END

As you can see, I am using the same query 3 times to populate 3 OUT parameters.

My question is, is there a way to do it with only one query?

Thanks.

EDIT: I see why INTO didn't work for me now. I got the syntax wrong.

Here's the working version:

CREATE PROCEDURE `t_get_normalized_client_id`(IN source_id INT, IN source_division VARCHAR(255), IN source_currency VARCHAR(255), OUT NORMALIZED_ID INT, OUT NAME VARCHAR(255), OUT ADDRESS VARCHAR(255))
BEGIN

IF source_id > 100000 THEN SET source_id = source_id - 100000; END IF;

SELECT
CLIENT.NEW_GROUP_REFERENCE_NUMBER, 
CLIENT.name,  
CONCAT(IFNULL(CLIENT.OPERATION_STREET,''), ' ',    IFNULL(CLIENT.OPERATION_CITY,''), ' ', 
IFNULL(CLIENT.OPERATION_STATE_PROVINCE, ''), ' ', IFNULL(CLIENT.OPERATION_ZIP, ''))

INTO 
NORMALIZED_ID,
NAME,
ADDRESS

FROM ccis_vendors.client_id SOURCE
INNER JOIN ccis_vendors.receivable CLIENT ON CLIENT.id = SOURCE.ACCOUNT_ID
WHERE SOURCE.number = source_id AND SOURCE.division = source_division;

END

Thanks everyone!

Upvotes: 1

Views: 121

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can simplify the selects down to a single one. And, I would recommend using consistent naming conventions for parameters -- so there is unlikely to be confusion with columns:

DELIMITER $$
CREATE DEFINER=`simonh`@`%` PROCEDURE `get_normalized_client_id`(
    IN in_source_id INT,
    IN in_source_division VARCHAR(255),
    IN in_source_currency VARCHAR(255),
    OUT out_NORMALIZED_ID INT,
    OUT out_NAME VARCHAR(255),
    OUT out_ADDRESS VARCHAR(255)
)
BEGIN
    IF in_source_id > 100000 THEN
        SET in_source_id = in_source_id - 100000;
    END IF;

    SELECT out_NORMALIZED_ID := CLIENT.NEW_GROUP_REFERENCE_NUMBER,
           out_Name := CLIENT.NAME,
           out_Address := CONCAT_WS(' ', CLIENT.OPERATION_STREET, CLIENT.OPERATION_CITY,
                                    CLIENT.OPERATION_STATE_PROVINCE, CLIENT.OPERATION_ZIP
                                   )
    FROM ccis_vendors.client_id SOURCE INNER JOIN
         ccis_vendors.receivable CLIENT
         ON CLIENT.id = SOURCE.ACCOUNT_ID
    WHERE SOURCE.number = in_source_id AND SOURCE.division = in_source_division;

END;$$
DELIMITER ;

In addition:

  • I put the IF on multiple lines. It is clearer that the END IF is there. In general, I put "end"s of things immediately under the "beginnings" to ensure proper closure.
  • CONCAT_WS() seems a better option than CONCAT(). As a bonus, it also handles NULL values.
  • I prefer the inline syntax of := rather than INTO. This is really just a matter of style. INTO is used for other purposes, such as file access, so I think := is clearer.

Upvotes: 1

Pat B
Pat B

Reputation: 1955

Between you Begin and End statements SELECT CLIENT.name INTO NAME, CLIENT.NEW_GROUP_REFERENCE_NUMBER INTO NORMALIZED_ID, CONCAT(IFNULL(CLIENT.OPERATION_STREET,''), ' ', IFNULL(CLIENT.OPERATION_CITY,''), ' ', IFNULL(CLIENT.OPERATION_STATE_PROVINCE, ''), ' ', IFNULL(CLIENT.OPERATION_ZIP, '')) INTO ADDRESS FROM ccis_vendors.client_id SOURCE INNER JOIN ccis_vendors.receivable CLIENT ON CLIENT.id = SOURCE.ACCOUNT_ID WHERE SOURCE.number = source_id AND SOURCE.division = source_division;

Upvotes: 0

The scion
The scion

Reputation: 972

Can't you do it like this?

 CREATE DEFINER=`simonh`@`%` PROCEDURE `get_normalized_client_id`(IN source_id INT, IN source_division VARCHAR(255), IN source_currency VARCHAR(255), OUT NORMALIZED_ID INT, OUT NAME VARCHAR(255), OUT ADDRESS VARCHAR(255))
    BEGIN

IF source_id > 100000 THEN SET source_id = source_id - 100000; END IF;

SELECT
CLIENT.NEW_GROUP_REFERENCE_NUMBER INTO NORMALIZED_ID,
CONCAT(IFNULL(CLIENT.OPERATION_STREET,''), ' ', IFNULL(CLIENT.OPERATION_CITY,''), ' ', 
IFNULL(CLIENT.OPERATION_STATE_PROVINCE, ''), ' ', IFNULL(CLIENT.OPERATION_ZIP, '')) INTO ADDRESS,
CLIENT.name INTO NAME
FROM ccis_vendors.client_id SOURCE
INNER JOIN ccis_vendors.receivable CLIENT ON CLIENT.id = SOURCE.ACCOUNT_ID
WHERE SOURCE.number = source_id AND SOURCE.division = source_division;


END

Upvotes: 1

Related Questions