0

Sorry for my poor english. Here's the problem: i'm trying to reciebe an ID in PHP from a table in sql server express 2014 via sqlsrv driver, an SP and a output parameter.

When i execute de SP and print the output parameter via SSMS everything works just fine. However, when i call the SP from PHP the SP is executed but the output parameter doesn't change.

This is the SP:

ALTER PROCEDURE [dbo].[SiguientePaciente](
    @IdExamen int OUTPUT
) AS
BEGIN
    SET NOCOUNT ON

    SET @IdExamen = (SELECT TOP 1 T.IdExamen FROM TURNOS T WHERE T.Estado = 0 ORDER BY T.FechaHora ASC)

    UPDATE TURNOS SET Estado = 1 WHERE IdExamen = @IdExamen

    RETURN
END

This is the PHP, assume that $conexion works:

$idExamen = 0;
$sql = "{CALL SiguientePaciente (?)}";
$params = [&$idExamen, SQLSRV_PARAM_OUT, SQLSRV_PHPTYPE_INT, SQLSRV_SQLTYPE_INT];
$stmt = sqlsrv_query($conexion, $sql, $params);
if($stmt !== false){
    //sqlsrv_next_result($stmt);
    echo $idExamen;
}
else
    echo print_r(sqlsrv_errors(), true);

In the end, echo $idExamen prints 0

EDIT: found an alternative way to do what i want, with a SELECT inside the SP. Then i just fetch de result in PHP

ALTER PROCEDURE [dbo].[SiguientePaciente](
    @IdExamen int OUTPUT
) AS
BEGIN
    SET NOCOUNT ON

    SET @IdExamen = (SELECT TOP 1 T.IdExamen FROM TURNOS T WHERE T.Estado = 0 ORDER BY T.FechaHora ASC)

    UPDATE TURNOS SET Estado = 1 WHERE IdExamen = @IdExamen

    SELECT @IdExamen AS IdExamen
    RETURN;
END
Nicolas Paz
  • 3
  • 4

1 Answers1

0

At this msdn link there is another way to assign your php $params array. I try to adapt that form to your case:

$params = array(   
                 array($idExamen, SQLSRV_PARAM_OUT)  
               );

It seems that doesn't need to apply referencing operator (I think) to the output variable when passing it, and other php constants related to the parameter..

update after comment

try also 

SELECT TOP 1 @IdExamen = T.IdExamen FROM TURNOS T WHERE T.Estado = 0 ORDER BY T.FechaHora ASC

it is a bit more compact..

Ciro Corvino
  • 2,038
  • 5
  • 20
  • 33
  • uhm, strange, i think i read somewhere about using the `&`. I just tried what you are saying and nothing. I managed to do this by putting a SELECT clause in the SP and fetching the result. However that's just an alternative way and therefore not the answer – Nicolas Paz Sep 28 '16 at 15:31
  • @NicolasPaz at this point, try to assign "@idExamen" in the first query so it is a bit more compact.. see my post updated – Ciro Corvino Sep 28 '16 at 15:43