将params传递到存储过程 - Passing params to stored procedure

Passing params to stored procedure

本文关键字：存储过程 params | 更新日期: 2023-09-27

我得到以下错误

SQLSTATE[42000]：[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]必须以'@name=value'的形式传递参数编号44和后续参数。在使用了"@name=value"形式后，所有后续参数都必须以"@name=value'"形式传递。

这是我的代码

$pdowin->beginTransaction();
    try {
        // headers
        $rows = $pdo->query("SELECT * FROM orders WHERE status = 2 AND productId IS NOT NULL GROUP BY id");
        // fetch the rows
        while ($row = $rows->fetch(PDO::FETCH_ASSOC)) {
            $pdowin->query("EXEC sp_salesorderimport
                @salesordernumberid = ".$row['id'].",
                    ... // number of other params here
                ");
            // items
            $items = $pdo->query("SELECT * FROM orders WHERE id = " . $row['id'] . " ORDER BY name ASC");
            while ($item = $items->fetch()) {
                $pdowin->query("EXEC sp_salesorderrowimport
                    @salesordernumberid = ".$row['id'].",
                    @articleid = ".$item['artid'].",
                        ... // number of other params here
                    ");
            }
        }
        $pdowin->commit();
        echo "OK";
    } catch (PDOException $e) {
        $pdowin->rollback();
        echo "ERROR: ".$e;
    }

如果我单独执行这些查询，所有查询都能工作。提前谢谢。

您应该准备您的声明：

$pdowin->beginTransaction();
    try {
        // headers
        $rows = $pdo->query("SELECT * FROM orders WHERE status = 2 AND productId IS NOT NULL GROUP BY id");
        $stmt = $pdowin->prepare("EXEC sp_salesorderimport
                @salesordernumberid = :saleId,
                    ... // number of other params here
                ");
        // fetch the rows
        while ($row = $rows->fetch(PDO::FETCH_ASSOC)) {
            $stmt->bindValue(':saleId',$row['id']);
            $stmt->execute();

它不仅用于防止sql注入，还可以避免引号和转义字符

的错误