php sqlsrv_query stored procedure with naming parameters

Question

Im using php 5.4 with sqlsrv extension and I'm trying to call this sample stored procedure (NorthWind database):

create  PROCEDURE [dbo].[GetCategories] 
@CategoryID int = null
AS
SELECT * from dbo.Categories where CategoryID= IsNull(@CategoryID,CategoryID)

And I'm using this sqlsrv_query syntax:

$sql = "{ call dbo.GetCategories (?)}";
$catID=2;
$params = array($catID);
$stmt = sqlsrv_query( $conn, $sql,$params);

I want to specify parameter name and value in $params, just like this:

$sql = "dbo.GetCategories";
$catID=2;
$params = array("@CategoryID"=>$catID);
$stmt = sqlsrv_query( $conn, $sql,$params);

It return this error: String keys are not allowed in parameters arrays.

How can i solve this problem? Thanks

Andrea Colabufalo · Accepted Answer · 2012-11-09 10:16:42Z

2

I found this solution using PDO:

$dbh = new PDO('sqlsrv:server= ...');
$sql = "{CALL dbo.GetCategories (@CategoryID=:CategoryID)}";
$stmt = $dbh ->prepare($sql);

$stmt->bindParam('CategoryID', $catID, PDO::PARAM_INT);

$stmt->execute();

$results = array();
do {
    $results []= $stmt->fetchAll();
} while ($stmt->nextRowset());

echo '<pre>';


echo($results[0][0]['CategoryID'] . ', '.
         $results[0][0]['CategoryName'] . ', '.
         $results[0][0]['Description']);
echo '</pre>';

$stmt->closeCursor();
unset($stmt);

answered Nov 9, 2012 at 10:16

Andrea Colabufalo

3584 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

iaina · Accepted Answer · 2018-01-30 20:26:55Z

1

You can't use associative arrays for binding values to SQL parameters. The elements in the array must be in the same order as the parameters.

answered Jan 30, 2018 at 20:26

iaina

1652 silver badges9 bronze badges

Collectives™ on Stack Overflow

php sqlsrv_query stored procedure with naming parameters

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related