CODEWITHSUNDEEP
phpsql-serverstored-proceduresphp-mssql
Karen Dixon
Karen Dixon

Reputation: 43

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

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.

Upvotes: 1

Views: 5248

Answers (1)

Nime Cloud
Nime Cloud

Reputation: 6395

For future reference: ADO CreateParameter Method

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

It's in VBScript but it might help others.

Upvotes: -1

Related Questions