Asked  1 Year ago    Answers:  5   Viewed   12 times

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

  • Get RETURN value from stored procedure in SQL
  • Get Return Value from SQL Stored Procedure using PHP

 Answers

5

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

Thursday, April 1, 2021
 
jonboy
 
3

If your stored procedure does not return anything you have to use SET NOCOUNT ON;

You can read more about it here:

https://msdn.microsoft.com/en-us/library/ms189837.aspx

Saturday, May 29, 2021
 
samrap
 
1

It finally worked with :

$result = odbc_exec($odbc,"EXECUTE importClient @name = 'hello', @number = 457");

But this isn't protected against SQL injections, so it might not be the best answer

Saturday, May 29, 2021
 
Litty
 
3

I found it! I can read the return value with an output parameter that has to be used in this way:

// define a new output parameter
var returnCode = new SqlParameter();
returnCode.ParameterName = "@ReturnCode";
returnCode.SqlDbType = SqlDbType.Int;
returnCode.Direction = ParameterDirection.Output;

// assign the return code to the new output parameter and pass it to the sp
var data = _context.Database.SqlQuery<Item>("exec @ReturnCode = spItemData @Code, @StatusLog OUT", returnCode, code, outParam);
Wednesday, June 2, 2021
 
Student
 
5

Its not the NOCOUNT thats causing this, your stored procedures have a select each so each one is coming in its own result set. This could be avoided by changing your first stored procedure to use output parameters to pass the number 1 back rather than doing a select. The second stored procedure could then examine the output parameter to get the data it needs to run.

Try something like this

CREATE PROCEDURE Proc1
(
    @RetVal INT OUTPUT
)
AS
SET NOCOUNT ON
SET @RetVal = 1


CREATE PROCEDURE Proc2
AS
SET NOCOUNT ON
DECLARE @RetVal int
EXEC    [dbo].[Proc1]
        @RetVal = @RetVal OUTPUT
SELECT  @RetVal as N'@RetVal'
Sunday, August 15, 2021
 
danjah
 
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :