Php – PDO Stored Procedure return value

pdoPHPsql serverstored-procedures

I'm working with a SQL Server stored procedure that returns error codes; here is a very simple snippet of the SP.

DECLARE @ret int
BEGIN
SET @ret = 1
RETURN @ret
END

I can get the return value with the mssql extension using:

mssql_bind($proc, "RETVAL", &$return, SQLINT2);

However, I can't figure out how to access the return value in PDO; I'd prefer not to use an OUT parameter, as alot of these Stored Procedures have already been written. Here is an example of how I am currently calling the procedure in PHP.

$stmt = $this->db->prepare("EXECUTE usp_myproc ?, ?");
$stmt->bindParam(1, 'mystr', PDO::PARAM_STR);
$stmt->bindParam(2, 'mystr2', PDO::PARAM_STR);
$rs = $stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

Best Answer

Check out MSDN for info on how to correctly bind to this type of call

Your PHP code should probably be tweaked to look more like this. This may only work if you're calling through ODBC, which is honestly the strongly preferred way to do anything with SQL Server; use the SQL Native Client on Windows systems, and use the FreeTDS ODBC driver on *nix systems:

<?php
  $stmt = $this->db->prepare("{?= CALL usp_myproc}");
  $stmt->bindParam(1, $retval, PDO::PARAM_STR, 32);
  $rs = $stmt->execute();
  $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
  echo "The return value is $retval\n";
?>

The key thing here is that the return value can be bound as an OUT parameter, without having to restructure the stored procedures.

Related Topic