Get Return Value from SQL Stored Procedure using PHP

Question

So I have a php script that uses a stored procedure to interact with my SQL database. The stored procedure works just fine, the problem is I don't know how to get my php to echo the Return Value from the stored procedure. The stored procedure is basically activating an account using an activation key and sets the username and password.

It basically says "if the activation key provided does not have a username yet, set it to the one provided and RETURN 1, if it already has a username RETURN 2, and if the activation key does not exist RETURN 3". It works perfectly in SQL and even give the correct Return Value. Now how can I get my php to echo that? I have tried the following:

$link = sqlsrv_connect($myServer, array('Database' => $myDB, 'UID' => $myUser, 'PWD' => $myPass));
if($link === FALSE) {
    die('Could not connect: ' . sqlsrv_errors(SQLSRV_ERR_ALL));
}

$key = $_REQUEST['key'];
$username = $_REQUEST['username'];
$password = $_REQUEST['password'];

$query = "EXEC Activate_Account";
$query .=" @key = ";
$query .= $key;
$query .=", @user = ";
$query .= $username;
$query .=", @pass = ";
$query .= $password;

$result = sqlsrv_query($link, $query);

while($row = sqlsrv_fetch_array($result))
{
    echo $row[0];
}

sqlsrv_close($link);

and

while($row = sqlsrv_fetch_array($result))
{
    echo $row['Return Value'];
}

Neither of them echo anything.

You havn't included the part of the program that is actually not working correctly, that is the part where you define $result. So include that code in your question. "Not echoing anything" is imprecise, what exactly is the value of $row? What is its datatype and is it a blankstring or is it an array of which you are not asking for the right key? — Eric Leschinski
– Eric Leschinski, Commented Mar 25, 2014 at 17:14

doydoy44 · Accepted Answer · 2016-10-29 12:27:08Z

7

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);

edited Oct 29, 2016 at 12:27

answered Mar 25, 2014 at 17:19

doydoy44

5,8004 gold badges31 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bart Heyrman · Accepted Answer · 2022-05-10 09:26:48Z

The following example enables you to retrieve the RETURN value from a stored procedure and allows you to retrieve the OUTPUT values.

SQL:

CREATE PROCEDURE [ProcedureName]
    @input_parameter as int,
    @output_parameter as int out
AS
BEGIN
    -- Your SQL code
    SELECT @output_parameter = @input_parameter;
    RETURN 2
END

PHP:

// Connect.
$link = sqlsrv_connect($myServer, array('Database' => $myDB, 'UID' => $myUser, 'PWD' => $myPass));

$returnValue = 0;
$inputParameter = 10;
$outputParameter = 0;

$parameters = [
    [&$returnValue, SQLSRV_PARAM_OUT],
    [$inputParameter, SQLSRV_PARAM_IN],
    [&$outputParameter, SQLSRV_PARAM_OUT],
];

$sql = "EXEC ? = ProcedureName ? ?";
$stmt = sqlsrv_query($link, $sql, $parameters);

if ($stmt === false)
{
    // Throw/Handle Error.
}

// $returnValue and $outputParameter should be updated here because they were passed by Reference.

// Retrieve query rows if any.
$rows = [];
while (($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC) ?? false) !== false) {
    $rows[] = $row;
}

Some more reading about this topic can be found on the following links: How to retrieve input and output parameters using the sqlsrv driver and Sqlsrv query

Collectives™ on Stack Overflow

Get Return Value from SQL Stored Procedure using PHP

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related