The formal parameter was not declared as an output parameter - SQL Server & PHP

Question

I'm having a problem with a stored procedure from SQL Server with PHP. I'm working on a system that takes order lines, generates an order header and then places the order lines into the SQL Server database. Everything is going fine, until I try to add each order line to the database. I've implemented the following code:

//Add code here to add the item to the order
// Create order header
/* prepare the statement resource */
$stmt=mssql_init("SPNAME1", $link);

/* now bind the parameters to it */
mssql_bind($stmt, "@CustNum",  $_SESSION['CustNum'],  SQLVARCHAR, FALSE);
mssql_bind($stmt, "@OrderCustSeq",  $_SESSION['CustSeq'],  SQLVARCHAR, FALSE);

if (isset($_REQUEST['TextBoxOrderNumber'])) {
    mssql_bind($stmt, "@OrderCustPo",  $_REQUEST['TextBoxOrderNumber'],  SQLVARCHAR, FALSE);
}

if (isset($_REQUEST['comboDispatchMethods'])) {
    mssql_bind($stmt, "@OrderShipVia",  $_REQUEST['comboDispatchMethods'],  SQLVARCHAR, FALSE);
} else {
    $_REQUEST['comboDispatchMethods'] = NULL;
    mssql_bind($stmt, "@OrderShipVia",  $_REQUEST['comboDispatchMethods'],  SQLVARCHAR, FALSE);
}

if (isset($_REQUEST['comboOrderTypes'])) {
    mssql_bind($stmt, "@OrderPriceCode",  $_REQUEST['comboOrderTypes'],  SQLVARCHAR, FALSE);
} else {
    $_REQUEST['comboOrderTypes'] = NULL;
    mssql_bind($stmt, "@OrderPriceCode",  $_REQUEST['comboOrderTypes'],  SQLVARCHAR, FALSE);
}

//now bind the output variables
mssql_bind($stmt, "@NewCoNumber", $NewCoNumber, SQLVARCHAR, true);

// now execute the procedure
$result = mssql_execute($stmt) or die ("Error in sp query: $query. " .mssql_get_last_message()); 

//Store each order line to the database
foreach( $_SESSION['orderarray'] as $obj )
{
    $SearchString = $obj['PartNo'];
    $qty = $obj['QtyReq'];

    /* prepare the statement resource */
    $addorderline=mssql_init("SPNAME2", $link);

    /* now bind the parameters to it */
    mssql_bind($addorderline, "@CoNum",    $NewCoNumber,    SQLVARCHAR,    FALSE);
    mssql_bind($addorderline, "@OrderItem",  $SearchString,  SQLVARCHAR, FALSE);
    mssql_bind($addorderline, "@QtyRequired", $qty, SQLFLT8, FALSE);

    // now execute the procedure
    $addorderresult = mssql_execute($addorderline) or die ("Error in sp query: $addorderline " .mssql_get_last_message()); 

    mssql_free_statement($addorderline);
    mssql_free_result($addorderresult); 
}

mssql_free_result($result);
mssql_free_statement($stmt);

When I try to add an order I the first stored procedure runs without any problems, but there I am getting an error reported from $addorderresult stating:

The formal parameter "@PriceCode" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output

At no point on the page is @PriceCode declared, although I do return $row['PriceCode'] in another portion of the code which is not executed when sending the data across for storage. Removing that reference does nothing to resolve the issue. In fact @PriceCode doesn't exist in any of the pages on the site. As I understand it the error should indicate that I am using @PriceCode as an output variable, but not declaring it as such. I tried binding @PriceCode to the SPNAME2 stored procedure, but got another error - this time stating that:

Procedure or Function SPNAME2 has too many arguments specified

Can anyone please suggest what I've missed here? This has been driving me nuts for the last day or so, and I really need to get it fixed so the client can have a look at this.

In the procedure declaration, are you giving the @PriceCode parameter as output i.e. are you using it in any output statements(select)? — Karan Punamiya, Jul 16 '12 at 09:47
Yes, although the chap dealing with the database has just made some changes which have removed this problem and created another still related to the output PriceCode from the select created by the stored procedure. — Karen Dixon, Jul 16 '12 at 09:54
Scratch that... problem solved, I'd reused some code & failed to notice that I needed to change 2 of the output parameters. Apparently the database needed some default parameters setting up that the DB admin had forgotten to put in! — Karen Dixon, Jul 16 '12 at 09:59

score -1 · Answer 1 · answered Mar 28 '13 at 07:02

-1

For future reference: ADO CreateParameter Method

http://www.w3schools.com/ado/met_comm_createparameter.asp

It's in VBScript but it might help others.

answered Mar 28 '13 at 07:02

Nime Cloud

6,162
14
43
75

The formal parameter was not declared as an output parameter - SQL Server & PHP

1 Answers1