How to execute a stored procedure in php using sqlsrv and "?" style parameters

The user contributions on the php.net have a write up on how to execute a stored procedure using the sqlsrv-prepare.

In case that is removed from the php.net user contributions in the future here is what it had(has) listed:

$procedure_params = array(
array(&$myparams['Item_ID'], SQLSRV_PARAM_OUT),
array(&$myparams['Item_Name'], SQLSRV_PARAM_OUT)
);
// EXEC the procedure, {call stp_Create_Item (@Item_ID = ?, @Item_Name = ?)} seems to fail with various errors in my experiments
$sql = "EXEC stp_Create_Item @Item_ID = ?, @Item_Name = ?";
$stmt = sqlsrv_prepare($conn, $sql, $procedure_params);

Here's the manual's page, http://php.net/manual/en/function.sqlsrv-prepare.php


This is a follow up to the answer by @chris85.

It's worth noting here that once the statement is prepared, you need to execute it:

$sql = "EXEC stp_Create_Item @Item_ID = ?, @Item_Name = ?";
$stmt = sqlsrv_prepare($conn, $sql, $procedure_params);
if (!sqlsrv_execute($stmt)) {
    echo "Your code is fail!";
    die;
}
while($row = sqlsrv_fetch_array($stmt)){
    //Stuff
}

sqlsrv_execute() only returns true/false. If you want to parse the data returned by the stored procedure you can process it just like the result from sqlsrv_query().

If you forget the sqlsrv_execute() you'll get an error saying that the result has to be executed before it can be used.


This is another followup to the answer by @chris85.

I tried the answer, combined with the followup answer by @AndyD273, but got the following error: I get the exception "The formal parameter "@param1" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output"

I solved this by changing all instances of SQLSRV_PARAM_OUT to SQLSRV_PARAM_IN. The documentation for SQLSRV_PARAM_IN says:

Indicates an input parameter when passed to sqlsrv_query() or sqlsrv_prepare().

The updated version of @chris85's answer to prepare the stored procedure parameters now looks like:

$procedure_params = array(
    array(&$myparams['Item_ID'], SQLSRV_PARAM_IN),
    array(&$myparams['Item_Name'], SQLSRV_PARAM_IN)
);