php中如何调用sql server,如何在PHP中使用PDO连接调用SQL Server存储过程?

调用过程使用

CALL

语法。

接下来是使用php 7.1.12、用于SQL Server 4.3.0+9904的php驱动程序、SQL Server 2012 Express Edition的工作示例。两者都是

{CALL sp_name}

和

EXEC sp_name

工作。

T-SQL:

CREATE PROCEDURE [dbo].[getAllUser]

AS BEGIN

SELECT 'Result from stored procedure.' AS [ResultText]

END

CREATE PROCEDURE [dbo].[getAllEvent]

@FromDate date,

@ToDate date,

@UserID int

AS

BEGIN

SELECT

@FromDate AS FromDate,

@ToDate AS ToDate,

@UserID AS UserID

END

PHP:

# Connection info

$hostname = 'server\instance,port';

$dbname = 'database';

$username = 'username';

$pw = 'password';

# Connection

try {

$dbh = new PDO("sqlsrv:server=$hostname;Database=$dbname", $username, $pw);

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

} catch( PDOException $e ) {

die( "Error connecting to SQL Server. ".$e->getMessage());

}

# First procedure

try {

echo "Procedure: getAllUser"."
";

$sql = "{CALL getAllUser}";

#$sql = "EXEC getAllUser";

$stmt = $dbh->query($sql);

# If your stored procedure returns information about records affected, fetch next result set

$stmt->nextRowset();

while ($row = $stmt->fetch( PDO::FETCH_ASSOC )) {

foreach($row as $name => $value) {

echo $name.": ".$value."
";

}

}

echo "
";

} catch( PDOException $e ) {

die( "Error executing stored procedure: ".$e->getMessage());

}

$stmt = null;

# Second procedure

try {

echo "Procedure: getAllEvent"."
";

$sql = "{CALL getAllEvent(?, ?, ?)}";

#$sql = "exec getAllEvent ?, ?, ?";

$fromDate = '2008-08-03';

$toDate = '2008-08-05';

$userID = 123;

$stmt = $dbh->prepare($sql);

$stmt->bindParam(1, $fromDate);

$stmt->bindParam(2, $toDate);

$stmt->bindParam(3, $userID);

$stmt->execute();

while ( $row = $stmt->fetch( PDO::FETCH_ASSOC ) ){

foreach($row as $name => $value) {

echo $name.": ".$value."
";

}

}

echo "
";

} catch( PDOException $e ) {

die( "Error connecting to SQL Server" );

}

$stmt = null;

# End

$dbh = null;

?>