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

Question

I've looked over several other questions that seem (from the titles) the same as this. However, my case is a bit different.

The following works (i.e. I get "success" and my database performs what I expect when running the procedure with the given variables):

$sql = "MyDB.dbo.myProcedure {$var1}, {$var2}, {$var3}";
$result = sqlsrv_query($myConn, $sql);
if (!$result) {
    echo 'Your code is fail.';
}
else {
    echo 'Success!';
}

I want to avoid (or lessen the possibility of) SQL injection by creating the SQL string using parameters. For example:

$sql = "select * from aTable where col1 = ? AND col2 = ?";
$result = sqlsrv_query($myConn, $sql, array($var1, $var2));
//please note. This code WILL work!

But when I do that with a stored procedure it fails. It fails with no errors reported via sqlsrv_errors(), no action taken in database, and $result === false.

To be clear, the following fails:

$sql = "MyDB.dbo.myProcedure ?, ?, ?";
$result = sqlsrv_query($myConn, $sql, array($var1, $var2, $var3));

Likewise a prepare/execute statement created the same way will also fail:

$sql = "MyDB.dbo.myProcedure ?, ?, ?";
$stmt = sqlsrv_prepare($myConn, $sql, array(&$var1, &$var2, &$var3));
foreach($someArray as $key => $var3) {
    if(sqlsrv_execute($stmt) === false) {
        echo 'mucho fail.';
    }
}
//this code also fails.

For completeness, I have confirmed that the stored procedure in question works directly within SQL Management Studio AND if called the way I mentioned above. Likewise, I have confirmed that I can use parameterized queries for any raw query (like an insert, select, update vs a stored procedure).

So, my question is how can I call a stored procedure using the parameterized query vs embedding the variables in the query string?

More importantly, I am actually wanting to use a prepare/execute, so hopefully the answer will allow this to work as well.

Have you tried like the example on the manual page? $sql = "EXEC stp_Create_Item @Item_ID = ?, @Item_Name = ?"; php.net/manual/en/function.sqlsrv-prepare.php ... or not the examples but from the User Contributed Notes — chris85
– chris85, Commented Jul 22, 2015 at 22:55
I read through the user contributed notes on the sqlsrv_query page, but obviously I did not read or notice this example on the page you linked. This apparently is the way to do this. Do you want to add this as an answer, so I can mark it for any future searches? Otherwise I'll do it. — LittleTreeX
– LittleTreeX, Commented Jul 23, 2015 at 14:50

chris85 · Accepted Answer · 2015-07-23 15:09:46Z

21

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

answered Jul 23, 2015 at 15:09

chris85

23.9k7 gold badges36 silver badges51 bronze badges

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

2 Comments

Matt Over a year ago

Are @item_ID and @Item_Name the variables from the stored procedure in SQL Server?

LittleTreeX Over a year ago

@Hiebs915 Yes. It doesn't work if you generically use the question mark, so you have to explicitly use the variable name = ?. You still have to supply the variables in the correct order as far as I know. I'm not certain why it works this way, but this is the solution.

AndyD273 · Accepted Answer · 2018-09-27 13:24:02Z

12

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.

edited Sep 27, 2018 at 13:24

answered Sep 21, 2016 at 13:50

AndyD273

7,27913 gold badges61 silver badges94 bronze badges

2 Comments

dansch Over a year ago

I think sqlsrv_fetch_array or just sqlsrv_fetch is what it is.

dansch Over a year ago

sqlsrv_fetch_rows doesn't exist. search it on google. It doesn't work on php 7.2

Zishi Wu · Accepted Answer · 2021-11-11 22:00:31Z

3

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

answered Nov 11, 2021 at 22:00

Zishi Wu

312 bronze badges

1 Comment

Mark M Aug 18 at 17:59

You also could just remove the SQLSRV_PARAM_OUT option, no? According to sqlsrv_query() documentation, under the $direction param: "The default value is SQLSRV_PARAM_IN." (ref: php.net/manual/en/function.sqlsrv-query.php)

David Northcutt · Accepted Answer · 2019-03-28 03:19:11Z

0

Make sure you set this or you will always get errors returned if the stored procedure has messages being returned.

sqlsrv_configure('WarningsReturnAsErrors',0);

//Full working code below

$sql = "{call NameOfDatabase.NameOfOwner.StoredProcedureName(?,?)}";

$params = array($param1, $param2); 

if ($stmt = sqlsrv_prepare($conn, $sql, $params)) {
    echo "Statement prepared.<br><br>\n";  

} else {  
    echo "Statement could not be prepared.\n";  
    die(print_r(sqlsrv_errors(), true));  
} 

if( sqlsrv_execute( $stmt ) === false ) {

    die( print_r( sqlsrv_errors(), true));

}else{

    print_r(sqlsrv_fetch_array($stmt));

}

edited Mar 28, 2019 at 3:19

answered Mar 28, 2019 at 3:08

David Northcutt

11 bronze badge

1 Comment

Scott Fleming Over a year ago

You don't specify how $param1 and $param2 are set.

Collectives™ on Stack Overflow

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

4 Answers 4

2 Comments

2 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related