使用pdo php执行过程后，bindparam变量的值为零

Question

I'm using bindParam to bind the return value of stored procedure once the statement is executed

But i'm getting zero , i've specified output variable of stored procedure as BIGINT

and i'm binding parameter like below

$sql = "{:retval = CALL sp_testProc()}";
$stmt->bindParam('retval', $proc_pass_val, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT, 4);

bindparam is taking Length of data type as last parameter, i'm passing 4 here, but it returns zero, don't know why

Could anybody help me on this

Thanks in Advance

Answer 1

This is what I've done to make it work, Hope this helps someone.

Note: Procedure defined in MSSQL server

Here I want email into a field inorder to get that in an array, you can omit this line select @myemail as student_email; and you can get the value of @myemail into $myemail

My Procedure:

Alter proc [dbo].[sp_test_success](
@Id int=1,
@myemail varchar(250)=null output
)
AS
BEGIN
    select @myemail=rtrim(email) from Student where StudentId=@Id;  
    select @myemail as student_email;-- i put this to get myemail into array 
END

Code:

$dbh = new PDO('sqlsrv:Server=Server;Database=database', 'UID', 'Pwd');
$stmt = $dbh->prepare("{CALL sp_test_success(@Id=:Id,@myemail=:myemail)}");

$Id = 4;
$stmt->bindParam('Id', $Id, PDO::PARAM_INT); 
$stmt->bindParam('myemail', $myemail, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 500); 
$stmt->execute();
$results = array();
do {
    $rows= $stmt->fetch();// default fetch type PDO::FETCH_BOTH
    $results[]= $rows;
} while ($stmt->nextRowset());

echo "<pre>"; print_r($results);

print "procedure returned $myemail
"; exit;