CODEWITHSUNDEEP
mysqlstored-procedures
b51n6h
b51n6h

Reputation: 23

in and out variable in procedure calls

I am using a stored procedure call as follows:

    DELIMITER // 
CREATE procedure getCustomer(NID varchar(200),Name varchar(200), OUT Flag INTEGER, OUT CID VARCHAR(200))  
BEGIN 
DECLARE id varchar(200); 
SET Flag = 0;
SET id = CONCAT(NID, '_' ,  Name); 
SELECT 1 INTO Flag FROM Customer WHERE customerID = id;
IF Flag = 1 THEN  
SET CID = id; 
ELSE
INSERT INTO Customer(NID, Name, customerID) VALUES(NID, Name, id); 
SET CID = id; 
END IF; 
END//

can you please tell me how to call IN, OUT variables in testing this procedure call? or simply how to test this procedure call using exec proceudre_name(parameter) format?

Upvotes: 0

Views: 4131

Answers (2)

Jeroen
Jeroen

Reputation: 1002

Example using IN:

DELIMITER //  
CREATE PROCEDURE `procWithIN` (IN var1 INT)  
BEGIN  
    UPDATE someTable set fldname = fldname + var1;
END//

CALL procWithIN(10);

Example using OUT:

DELIMITER //  
CREATE PROCEDURE `procWithOUT` (OUT var1 VARCHAR(100))  
BEGIN  
    SET var1 = 'This is a test';  
END //

SET @someText = NULL;
CALL procWithOut(@someText);

-- do something with @someText (containing 'This is a test')
SELECT * FROM tbl WHERE fld=@someText

Example with INOUT

DELIMITER //  

CREATE PROCEDURE `procWithINOUT` (INOUT var1 INT)  
BEGIN  
    SET var1 = var1 + 100;  
END //

SET @someInt = 10;
CALL procWithINOUT(@someInt);
-- @someInt now contains 110

Upvotes: 0

eggyal
eggyal

Reputation: 125865

As documented under CALL Syntax:

To get back a value from a procedure using an OUT or INOUT parameter, pass the parameter by means of a user variable, and then check the value of the variable after the procedure returns. (If you are calling the procedure from within another stored procedure or function, you can also pass a routine parameter or local routine variable as an IN or INOUT parameter.) For an INOUT parameter, initialize its value before passing it to the procedure. The following procedure has an OUT parameter that the procedure sets to the current server version, and an INOUT value that the procedure increments by one from its current value:

CREATE PROCEDURE p (OUT ver_param VARCHAR(25), INOUT incr_param INT)
BEGIN
  # Set value of OUT parameter
  SELECT VERSION() INTO ver_param;
  # Increment value of INOUT parameter
  SET incr_param = incr_param + 1;
END;

Before calling the procedure, initialize the variable to be passed as the INOUT parameter. After calling the procedure, the values of the two variables will have been set or modified:


mysql> SET @increment = 10;
mysql> CALL p(@version, @increment);
mysql> SELECT @version, @increment;
+--------------+------------+
| @version     | @increment |
+--------------+------------+
| 5.5.3-m3-log |         11 |
+--------------+------------+

However, from your procedure it looks as though what you really want is to define a uniqueness constraint over the customerID column in your Customer table and then use INSERT ... ON DUPLICATE KEY UPDATE:

ALTER TABLE Customer ADD UNIQUE (customerID);

INSERT INTO Customer
  (NID, Name, customerID)
VALUES
  (123, 'foobar', CONCAT(123, '_', 'foobar'))
ON DUPLICATE KEY UPDATE
  NID = NID
;

Upvotes: 1

Related Questions