使用PHP从SQL存储过程获取返回值

问题描述

因此，我有一个使用存储过程与SQL数据库进行交互的php脚本.存储过程工作正常，问题是我不知道如何获取PHP以回显存储过程的返回值.存储过程基本上是使用激活密钥来激活帐户并设置用户名和密码.

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.

它基本上说:如果提供的激活密钥还没有用户名，请将其设置为提供的用户名，然后返回RETURN 1；如果已经具有用户名RETURN 2，并且激活密钥不存在，则返回RETURN 3".它在SQL中完美运行，甚至给出正确的返回值.现在我该如何获取我的PHP来回显呢?我尝试了以下方法:

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

和

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

他们俩都没有回声.

推荐答案

要使用存储过程返回值:

例如:

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代码:

$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从SQL存储过程获取返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！