CODEWITHSUNDEEP
mysqlselectstored-proceduresinsert
dpk
dpk

Reputation: 339

Stored procedure using insert and select statements

I want to get the adminID from ADMIN table using the email and save the adminID in the product table. I tried do that, it returns null in the adminID in Product table. Please let me know where I'm making a mistake.

CREATE PROCEDURE prc_saveProduct
(
    IN inputuserEmail VARCHAR(255),
    inputproductName VARCHAR(255),
    inputpoints INT(11),
)
BEGIN 
    SET @AdminID = (SELECT AdminID FROM ADMIN WHERE email = inputuserEmail );

    INSERT INTO PRODUCTS(AdminID, productName, points)
    VALUES (@AdminID, inputproductName, inputpoints);
END

Upvotes: 0

Views: 37

Answers (1)

user2278120
user2278120

Reputation: 653

This will work. 

DELIMITER $$

CREATE PROCEDURE `yourSchema`.`prc_saveProduct`(
    p_inputuserEmail VARCHAR(255), 
    p_inputproductname VARCHAR(255),
    p_inputpoints INT(11)
)
    BEGIN

    DECLARE _AdminID VARCHAR(255);

    SELECT
        AdminID FROM ADMIN WHERE email = p_inputuserEmail
    INTO
        _AdminID;

    INSERT INTO PRODUCTS(
    AdminID, 
    productName, 
    points
    )
    VALUES
    (
    _AdminID,
    p_inputproductName,
    p_inputpoints
    );  


    END$$

DELIMITER ;

the p_ is not necessary, I just like to prepend proc params with it to distinguish it. The underscore I like to use for local variables "_AdminID", but that's not necessary either. I just prefer those naming conventions.

Upvotes: 2

Related Questions