CODEWITHSUNDEEP
phpsql-serverstored-procedurespdo
Chris
Chris

Reputation:

PDO Stored Procedure return value

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

Upvotes: 6

Views: 19263

Answers (8)

Haddock-san
Haddock-san

Reputation: 895

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!

Upvotes: 0

luizschmitt
luizschmitt

Reputation: 1

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

Upvotes: 0

kevin.key
kevin.key

Reputation: 385

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;
}
?>

Upvotes: 1

Alex Weber
Alex Weber

Reputation: 2186

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

Upvotes: 0

Wez Furlong
Wez Furlong

Reputation: 4987

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.

Upvotes: 4

Stu
Stu

Reputation: 15778

Try $return_value

Upvotes: -1

stunnaman
stunnaman

Reputation: 911

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

Upvotes: 0

waqasahmed
waqasahmed

Reputation: 3855

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.

Upvotes: 0

Related Questions