Calling a stored procedure with SQLSRV

问题:

So I have a stored procedure I'd like to call, it takes one argument as an integer and returns 10 companies. Here's the SP inside MSSQL http://pastebin.com/shhakP81

here's my sql statement

$companiesSQL = "{call WEBgetCompanylistByIndIDTen( ? )}";

the parameter

$params = array(array($industryID, SQLSRV_PARAM_IN));

the query

$companiesStmt = sqlsrv_query($companiesHandle, $companiesSQL, $params);

and trying to print out the results gets me an error stating that

"sqlsrv_fetch_object() expects parameter 1 to be resource, boolean given"

I know that $companiesStmt SHOULD be a statement resource, but it's a boolean, which means that the query failed.

while($row = sqlsrv_fetch_object($companiesStmt))
{
    echo $row->COM."<br />";
}

I know that the connection works because I am able to call a different stored procedure in that table that has no parameters and get results. This means that the error can only be inside of $companiesSQL or $params

I'd also like to mention that Ive gone through a tleast the first 4-5 pages of google trying countless different ideas I have read including Microsoft's example on getting results from a SP (http://technet.microsoft.com/en-us/library/cc626303(v=sql.105).aspx) and even PHP's official website and its comments.

*edit I should also mention that if I login to MSSQL Server Management studio, and run the stored procedure from there, then it works.

What am I doing wrong here?

回答1:

First thing turn on error reporting. Make sure that you have sufficient privileges to use this stored procedure. Check that the account used to create the connection has authorization to execute the stored procedure and access to all the resources used by the stored procedure. I hope this helps, I ran into a similar problem when I started using SQL server. Instead of failing it was returning an empty result set.

**Edit

回答2:

I've had a similar error.

Try changing this line:

$companiesStmt = sqlsrv_query($companiesHandle, $companiesSQL, $params);

To this with no params:

$companiesStmt = sqlsrv_query($companiesHandle, $companiesSQL);

回答3:

Try adding OUT parameter to the $params array.

Look here for reference: http://forums.devshed.com/php-development-5/php-mssql-sqlsrv-stored-procedure-943084.html