Get Return Value from SQL Stored Procedure Using PHP

Get Return Value from SQL Stored Procedure using PHP

To return a value with a stored procedure:

For example:

SQL :

CREATE DEFINER=`user`@`localhost` PROCEDURE `ProcedureName`(IN `Input_Value` INT, OUT `Out_val` INT)
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
// Your SQL Code

    SET Out_val= Your Value;
    SELECT Out_val;
END

PHP Code:

$insert = "CALL ProcedureName(:Input_Value,
                             @Out_val)";
$bdd = new PDO('mysql:host=localhost;dbname=db-name', 'user', 'password');

$stmt = $bdd->prepare($insert);     
$stmt->bindParam(':Input_Value', $an_input_value, PDO::PARAM_STR); 

$stmt->execute();
$tabResultat = $stmt->fetch();
$Out_val = $tabResultat['Out_val'];
var_dump($Out_val);

PHP get return value of stored procedure

Execute stored procedure like this: "exec ?=mleko_test(?)".

Working example:

<?php
#------------------------------
# Connection info
#------------------------------
$server = 'server\instance,port';
$database = 'database';
$uid = 'user';
$pwd = 'password';

#------------------------------
# With PDO
#------------------------------
try {
    $conn = new PDO("sqlsrv:server=$server;Database=$database", $uid, $pwd);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch ( PDOException $e ) {
    die ( "Error connecting to SQL Server" );
}

try {
    $sql = "exec ? = mleko_test (?)";
    $param = 3;
    $spresult = 0;
    $stmt = $conn->prepare($sql);
    $stmt->bindParam(1, $spresult, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT, PDO::SQLSRV_PARAM_OUT_DEFAULT_SIZE);
    $stmt->bindParam(2, $param);
    $stmt->execute();
} catch ( PDOException $e ) {
    die ( "Error connecting to SQL Server" );
}

$stmt = null;
$conn = null;

echo 'Stored procedure return value (with PDO): '.$spresult."</br>";

#------------------------------
# Without PDO
#------------------------------
$cinfo = array (
    "Database" => $database,
    "UID" => $uid,
    "PWD" => $pwd
);

$conn = sqlsrv_connect($server, $cinfo);
if ( $conn === false )
{
    echo "Error (sqlsrv_connect): ".print_r(sqlsrv_errors(), true);
    exit;
}

$sql = "exec ? = mleko_test (?)";
$param = 3;
$spresult = 0;
$params = array(   
    array(&$spresult, SQLSRV_PARAM_OUT), 
    array($param, SQLSRV_PARAM_IN),  
); 
$stmt = sqlsrv_query($conn, $sql, $params);
if ( $stmt === false ) {
    echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
    exit;
}

while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
}

sqlsrv_free_stmt($stmt);
sqlsrv_close($conn);

echo 'Stored procedure return value (without PDO): '.$spresult."</br>";
?>

Notes:

Tested with PHP 7.1.12 and PHP Driver for SQL Server (pdo_sqlsrv version 4.3.0+9904).

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.

Get return value from mySQL stored procedure in PHP

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

PHP Get return value from MSSQL Stored Procedure

New:

I missed that you are using a single parameter as both input and output. Please try the following.

array($outSeq, SQLSRV_PARAM_INOUT)

Then using

sqlsrv_next_result($stmt);
echo $outSeq;

Reference:
http://technet.microsoft.com/en-us/library/cc644932(v=sql.105).aspx

Old:

You must set up $outSeq with the appropriate data type. Try initialize the value to $outSeq = 0.00, since your output type is MONEY.

Please reference the following article:

http://technet.microsoft.com/en-us/library/cc626303(v=sql.105).aspx

How to get output values from a MS SQL stored procedure using PHP?

Declare OUTPUT parameter in your SP's parameter list. The OUTPUT parameter return data back to the calling application. check this Link for more info

CREATE PROCEDURE checkFollowing 
(
    @myMemberID INT,
    @otherMemberID INT,
    @output INT OUTPUT
)
AS
BEGIN

IF EXISTS (SELECT 1 FROM Followers WHERE follower = @myMemberID AND followed = @otherMemberID)
    SELECT @output= 1
ELSE
    SELECT @output= 0    
END 
GO

Update:

To Call the SP and to store the Output. Try something like this

DECLARE @appout int; -- variable to hold the data returned by SP.

EXEC checkFollowing 10,20, @output = @appout OUTPUT; -- This is how i will call a SP.

@appout - will hold the data returned by the procedure.

---

Related Topics