PDO Stored Procedure return value

Question

Hey

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);

Answer 1

can't u use SELECT to return the results? Then you can use a dataset (resultset in php?) to pick it up? I don't know know PHP, but in c# its quite simple - use a dataset.

Answer 2

pretty sure PDO::exec only returns number of rows.. this would be $rs in your example

Answer 3

Try $return_value

Answer 4

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.

Answer 5

If I understand your question properly you shouldn't have to call fetchAll()...

$stmt = $this->db->prepare("EXECUTE usp_myproc ?, ?");
$stmt->bindParam(1, $mystr, PDO::PARAM_STR);
$stmt->bindParam(2, $mystr2, PDO::PARAM_STR);
$rs = $stmt->execute();
echo "The return values are: $mystr , and: $mystr2";

PDOStatement::bindParam