PHP MSSQL 操作類別：支援預存程序與參數綁定

MssqlUtil 類別完整程式碼

<?php
class MssqlUtil
{
    private string $host;
    private string $user;
    private string $pass;
    private string $db;
    private $link;

    public function __construct(string $host, string $user, string $pass, string $db)
    {
        $this->host = $host;
        $this->user = $user;
        $this->pass = $pass;
        $this->db = $db;
    }

    private function connect(): void
    {
        $this->link = mssql_connect($this->host, $this->user, $this->pass);
        if (!$this->link) die('MSSQL 連線失敗');
        if (!mssql_select_db($this->db, $this->link)) die('資料庫不存在');
    }

    private function close(): void { mssql_close($this->link); }

    /** 帶參數預存程序 */
    public function executeProcedur(string $procName, array $params, array $types): array
    {
        $this->connect();
        $stmt = mssql_init($procName, $this->link);
        
        foreach ($params as $i => [$name, $value]) {
            mssql_bind($stmt, $name, $value, $types[$i]);
        }
        
        $res = mssql_execute($stmt);
        $rows = [];
        while ($row = mssql_fetch_assoc($res)) $rows[] = $row;
        
        mssql_free_result($res);
        mssql_free_statement($stmt);
        $this->close();
        return $rows;
    }

    /** 無參數預存程序 */
    public function executeProcedurNoPar(string $procName): array
    {
        $this->connect();
        $stmt = mssql_init($procName, $this->link);
        $res = mssql_execute($stmt);
        $rows = [];
        while ($row = mssql_fetch_assoc($res)) $rows[] = $row;
        mssql_free_result($res);
        mssql_free_statement($stmt);
        $this->close();
        return $rows;
    }

    /** 通用查詢 */
    public function query(string $sql)
    {
        $this->connect();
        $res = mssql_query($sql, $this->link);
        $this->close();
        return $res;
    }

    public function getResult(string $sql): array
    {
        $res = $this->query($sql);
        $rows = [];
        while ($row = mssql_fetch_assoc($res)) $rows[] = $row;
        mssql_free_result($res);
        return $rows;
    }

    public function getRowArray(string $sql): array
    {
        $res = $this->query($sql);
        $row = mssql_fetch_row($res);
        mssql_free_result($res);
        return $row;
    }

    public function executeSql(string $sql)
    {
        return $this->query($sql);
    }

    public function querySql(string $sql): int
    {
        $this->connect();
        mssql_query($sql, $this->link);
        $affected = mssql_rows_affected($this->link);
        $this->close();
        return $affected;
    }
}
?>

使用範例

<?php
require 'MssqlUtil.php';

$db = new MssqlUtil('127.0.0.1:1433', 'sa', 'password', 'mydb');

// 帶參數預存程序
$params = [
    ['@user_id', 123],
    ['@status', 'active']
];
$types = [SQLINT4, SQLVARCHAR]; // 對應 mssql_bind 類型常數
$rows = $db->executeProcedur('sp_get_user_orders', $params, $types);

// 無參數預存程序
$rows = $db->executeProcedurNoPar('sp_cleanup_logs');

// 一般查詢
$users = $db->getResult('SELECT * FROM users WHERE active = 1');
?>

重要提醒

• mssql 擴充已移除於 PHP 7.0+，現代專案請改用 sqlsrv (Microsoft 官方) 或 PDO_SQLSRV
• 參數陣列建議結構化：[['@param_name', $value], ...] 確保順序與類型對應
• SQLVARCHAR、SQLINT4 等為 mssql 擴充定義常數，勿加引號
• 生產環境請加入連線池、錯誤重試、記錄日誌

現代替代：PDO_SQLSRV 範例

$pdo = new PDO('sqlsrv:Server=127.0.0.1;Database=mydb', 'sa', 'password');
$stmt = $pdo->prepare('EXEC sp_get_user_orders @user_id = ?, @status = ?');
$stmt->execute([123, 'active']);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);