PDO Stored Procedure return value

Question

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

I am having similar problem, see: http://stackoverflow.com/questions/21466503/calling-stored-procedure-from-php-using-pdo-to-mssql-server-using-input-paramter — Michael Fever, Jan 30 '14 at 20:27

score 4 · Answer 1 · answered Aug 22 '09 at 01:50

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.

Also, as mentionned [on another post](https://social.msdn.microsoft.com/Forums/sqlserver/en-US/55f21fc5-dbc0-4fe4-a4ae-f15905a4293a/return-value-param-from-stored-procedure-is-not-updated-when-a-value-is-returned?forum=sqldriverforphp#6dde4297-b870-439d-bb03-6313ef544ab9), you have to specify length of output param and not specify length of input param (I kept getting the error "The formal parameter “@myParam” was not declared as an OUTPUT parameter, but the actual parameter passed in requested output" and omitting input parameter length solved it...) — Daniel, Dec 20 '16 at 13:12

score 1 · Answer 2 · answered Jun 06 '12 at 21:17

Just had this same problem:

<?php

function exec_sproc($sproc, $in_params)
{
   global $database;

   $stmnt = $database->prepare("EXEC " . $sproc);
   if($stmnt->execute($in_params))
   {
      if($row = $stmnt->fetch())
      {
         return $row[0];
      }
   }

   return -1;
}
?>

score 0 · Answer 3 · answered Jun 17 '09 at 01:46

0

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

answered Jun 17 '09 at 01:46

stunnaman

911
3
13
19

score 0 · Answer 4 · answered Oct 27 '09 at 02:34

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

score 0 · Answer 5 · edited Mar 08 '17 at 20:18

public function callProcedure($sp_name = null, $sp_args = []) {
    try {
        for($i = 0; $i < count($sp_args); $i++) {
            $o[] = '?';
        }

        $args = implode(',', $o);
        $sth = $connection->prepare("CALL $sp_name($args)");

        for($i = 0, $z =1; $i < count($sp_args); $i++, $z++) {
            $sth->bindParam($z, $sp_args[$i], \PDO::PARAM_STR|\PDO::PARAM_INPUT_OUTPUT, 2000);
        }

        if($sth->execute()) {
            return $sp_args;
        }
    } catch (PDOException $e) {
        this->error[] = $e->getMessage();
    }
}

Haddock-san · Answer 6 · 2019-12-13T11:20:52.533

I had a similar problem and was able to solve it by returning the execute like so...

function my_function(){
    $stmt = $this->db->prepare("EXECUTE usp_myproc ?, ?");
    $stmt->bindParam(1, 'mystr', PDO::PARAM_STR);
    $stmt->bindParam(2, 'mystr2', PDO::PARAM_STR);
    return $stmt->execute();
}

All that is left is to call the function using a variable and then analyse said variable.

$result = my_function();

You can now analyse the contents of $result to find the information you're looking for. Please let me know if this helps!

score 0 · Answer 7 · answered Jun 15 '09 at 21:21

0

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.

answered Jun 15 '09 at 21:21

waqasahmed

3,555
6
32
52

score -1 · Answer 8 · answered Jun 17 '09 at 17:39

-1

Try $return_value

answered Jun 17 '09 at 17:39

Stu

15,675
4
43
74

PDO Stored Procedure return value

8 Answers8