Return Value from MySQL Stored Procedure

MySQL stored procedure return value

You have done the stored procedure correctly but I think you have not referenced the valido variable properly. I was looking at some examples and they have put an @ symbol before the parameter like this @Valido

This statement SELECT valido; should be like this SELECT @valido;

Look at this link mysql stored-procedure: out parameter. Notice the solution with 7 upvotes. He has reference the parameter with an @ sign, hence I suggested you add an @ sign before your parameter valido

I hope that works for you. if it does vote up and mark it as the answer. If not, tell me.

MySQL Stored Procedure return value without a SELECT

You can create function instead:

DELIMITER $$
CREATE FUNCTION `getroom`() RETURNS INT
BEGIN
    DECLARE free_room INT;
    SELECT room INTO free_room FROM admins WHERE free = 1 LIMIT 1 FOR UPDATE;
    UPDATE admins SET free = 0 WHERE room = free_room;
    RETURN free_room;
END$$
DELIMITER ;

and then just use:

SELECT getroom();

How can I get and assign the return value from MySQL stored procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `getUserIdByLogin`(
   userId VARCHAR(255),
   OUT idout int
)
BEGIN
    SELECT id INTO idout FROM `userdata` WHERE login = userId;
END

then

SET @id = 0;
CALL getUserIdByLogin("someLogin",  @id);
SELECT @id;

Accessing Returned value from MySQL Stored Procedure in PHP

Have been able to solve it. And this is how I did it.

CREATE PROCEDURE `shopping_cart_create_order2`(IN `inCartId` INT(11), OUT `newOrderId` INT(11)) BEGIN
    DECLARE newOrder int;
    -- Insert a new record into orders and obtain the new order ID
    INSERT INTO orders (created_on) VALUES (NOW());

    -- Obtain the new Order ID
    SELECT LAST_INSERT_ID() INTO newOrder;

    -- Insert order details in order_detail table
    INSERT INTO order_detail (
        order_id, product_id, attributes, product_name, quantity, unit_cost
    )
    SELECT
        newOrder,
        p.id,
        sc.attributes,
        p.name,
        sc.quantity,
        COALESCE( NULLIF( p.discounted_price, 0 ), p.price ) AS unit_cost
    FROM
        shopping_cart sc
        INNER JOIN products p ON sc.product_id = p.id
    WHERE
        sc.cart_id = inCartId
        AND
        sc.buy_now;

    -- Save the order's total amount
    UPDATE
     orders
    SET
        total_amount = (
            SELECT
                SUM( unit_cost * quantity )
            FROM
                order_detail
            WHERE
                order_id = newOrder
        )
    WHERE
        id = newOrder;

    -- Clear the shopping cart
    CALL shopping_cart_empty(inCartId);
    SET newOrder = newOrderId;
END

At PHP level// Probably at the Model/Entity level
First, we need to execute the

shopping_cart_create_order2()

stored procedure. Which might probably be in a function.

Second, to get the last order id, we need to query it from the variable

@oid

. It is important that we must call the method

closeCursor()

of the PDOStatement object in order to execute the next SQL statement.

function query($pdo, $sql, $parameters = []){
    $query = $pdo->prepare($sql);
    $query->execute($parameters);
    return $query;
}

function create_order($pdo, $cart_id){
    // Binding the parameters
    $parameters = [':cart_id' => $cart_id];

    // calling stored procedure command
    $sql = 'CALL shopping_cart_create_order2(:cart_id)';

    // prepare for execution of the stored procedure, pass value to the command 
    and execute the Stored Procedure
    $query = query($pdo, $sql, $parameters);

   // Then close Cursor. It is important for you to close it.
    $query->closeCursor();

   // execute the second query to get last insert id
    $row = $pdo->query("SELECT @oid AS oid")->fetch();
    return $row;    
}

For more info, see enter link description here

I hope am able to help someone out there who might probably fall into what I passed through.

Returning value from Stored Procedure and using it in C#

try the code below. If it works, please mark the answer as positive. Thank you!

    public ActionResult Recover(string UserEmail)
    {
        Login model = new Login();
        MySqlConnection connect = DbConnection();
        MySqlCommand cmd = new MySqlCommand("Recover_PassWord",connect);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@Email", UserEmail);
        MySqlDataReader rd = cmd.ExecuteReader();
        while (rd.Read())
        {
            if (UserEmail == rd["ContactEmail"].ToString())
            {
                try
                {
                    SmtpClient mySmtpClient = new SmtpClient("my.smtp.exampleserver.net");

                    // set smtp-client with basicAuthentication
                    mySmtpClient.UseDefaultCredentials = false;
                    System.Net.NetworkCredential basicAuthenticationInfo = new System.Net.NetworkCredential("username", "password");
                    mySmtpClient.Credentials = basicAuthenticationInfo;                    

                    MailMessage msg = new MailMessage();
                    msg.To.Add(UserEmail);
                    msg.Subject = "Recovered Login Information";
                    msg.Body = "Hello," + Environment.NewLine + "Your Login Information: "; //this is where i want to be able to use the returned value

                    msg.Body += Environment.NewLine + "username:" + (string)rd["username"];
                    msg.Body += Environment.NewLine + "password:" + (string)rd["password"];          

                    mySmtpClient.Send(msg);

                }
                catch (SmtpException ex)
                {
                  throw new ApplicationException
                    ("SmtpException has occured: " + ex.Message);
                }
                catch (Exception ex)
                {
                   throw ex;
                }
            }
        }
        return null;
    }

Get return value from mySQL stored procedure in PHP

My edited code was working correctly.
I just need to comment "var_dump($users);"

---

Related Topics