I have pleasure to work with legacy PHP application using SQL Server via PDO.
How in PHP can I retrieve return value of stored procedure which is using RETURN
statement as output channel?
Example procedure
CREATE PROCEDURE [dbo].[mleko_test] @param INT AS BEGIN RETURN @param * 3; END GO
If possible, I would prefer to not modify procedure.
I am aware that there are similar questions, but they don’t cover this case
Advertisement
Answer
Execute stored procedure like this: "exec ?=mleko_test(?)"
.
Working example:
<?php #------------------------------ # Connection info #------------------------------ $server = 'serverinstance,port'; $database = 'database'; $uid = 'user'; $pwd = 'password'; #------------------------------ # With PDO #------------------------------ try { $conn = new PDO("sqlsrv:server=$server;Database=$database", $uid, $pwd); $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch ( PDOException $e ) { die ( "Error connecting to SQL Server" ); } try { $sql = "exec ? = mleko_test (?)"; $param = 3; $spresult = 0; $stmt = $conn->prepare($sql); $stmt->bindParam(1, $spresult, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT, PDO::SQLSRV_PARAM_OUT_DEFAULT_SIZE); $stmt->bindParam(2, $param); $stmt->execute(); } catch ( PDOException $e ) { die ( "Error connecting to SQL Server" ); } $stmt = null; $conn = null; echo 'Stored procedure return value (with PDO): '.$spresult."</br>"; #------------------------------ # Without PDO #------------------------------ $cinfo = array ( "Database" => $database, "UID" => $uid, "PWD" => $pwd ); $conn = sqlsrv_connect($server, $cinfo); if ( $conn === false ) { echo "Error (sqlsrv_connect): ".print_r(sqlsrv_errors(), true); exit; } $sql = "exec ? = mleko_test (?)"; $param = 3; $spresult = 0; $params = array( array(&$spresult, SQLSRV_PARAM_OUT), array($param, SQLSRV_PARAM_IN), ); $stmt = sqlsrv_query($conn, $sql, $params); if ( $stmt === false ) { echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true); exit; } while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) { } sqlsrv_free_stmt($stmt); sqlsrv_close($conn); echo 'Stored procedure return value (without PDO): '.$spresult."</br>"; ?>
Notes:
Tested with PHP 7.1.12 and PHP Driver for SQL Server (pdo_sqlsrv version 4.3.0+9904).