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

2021-12-20 00:00:00 sql return-value php sql-server stored-procedures

所以我有一个使用存储过程与我的 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.

它基本上是说如果提供的激活密钥还没有用户名，请将其设置为提供的用户名并返回 1，如果它已经有用户名返回 2，如果激活密钥不存在返回 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);

相关文章