Calling a MSSQL Stored Procedure with multiple inputs and outputs

[Thomas_Bode]

    $myPMSource = "FANESTRA5";   
    $period = -8;
    
    $myDB = "dog";
    $myCLSource = "cat";

    $sql = "{CALL retrieveproductiondata(?,?)}";
    $params = array(array($myPMSource, SQLSRV_PARAM_IN, $period, SQLSRV_PARAM_IN), array($myDB, SQLSRV_PARAM_INOUT, $myCLSource, SQLSRV_PARAM_INOUT));
    $stmt = sqlsrv_query($aconn, $sql, $params);

 

All of the web examples show and work with just one parameter of each. I have not been able to find the proper syntax for multiples.

[requinix]

Have you tried adding more?

[Thomas_Bode]

Adding more what?

This is an error msg  i get: In read_production_data
Array ( [0] => Array ( [0] => IMSSP [SQLSTATE] => IMSSP [1] => -16

 => -16 [2] => An invalid PHP type for parameter 1 was specified. [message] => An invalid PHP type for parameter 1 was specified. ) )

[gizmola]

He meant, did you try adding more parameters to the array.  You have a basic misunderstanding of the manual:  https://www.php.net/manual/en/function.sqlsrv-query.php

"params" is an array of arrays, where each array element represents one, in, out or in/out parameter.  Your code seems to imply that you think there are separate arrays for input and output.  That is not the case.  There is only one array, and you need a child array for each parameter (in order of declaration for the sproc).

Since you did not provide us the source of the sproc, we don't know how it is defined.  Just trying to intuit what you are doing from your sample code, this looks more likely to work:

 

$params = array(
    array($myPMSource, SQLSRV_PARAM_IN),
    array($period, SQLSRV_PARAM_IN), 
    array($myDB, SQLSRV_PARAM_INOUT), 
    array($myCLSource, SQLSRV_PARAM_INOUT)
);

This style requires that the sql server driver assume the php and mssql server data types from a preset of the variables, but there are optional parameters (3 and 4) that can be used with constants documented on the query page, you could pass to explicitly tell the driver what is expected, within each single parameter array.

I haven't done any PHP with mssql in a long time, but it is possible that for any output variables you may need to pass those by reference.  That at least is what the current microsoft documentation shows.  So you might need something like this:

$params = array(
    array($myPMSource, SQLSRV_PARAM_IN),
    array($period, SQLSRV_PARAM_IN), 
    array(&$myDB, SQLSRV_PARAM_INOUT), 
    array(&$myCLSource, SQLSRV_PARAM_INOUT)
);

Last but not least, mssql does have a PDO driver, and that might be simpler and easier to use, as you would simply bind each parameter prior to the call.  See: https://learn.microsoft.com/en-us/sql/connect/php/pdo-sqlsrv-driver-reference?view=sql-server-ver16