2

I have created a stored procedure with a few input parameters and an output parameter. When I am trying to call it from PHP gives me an error. My code is as below :

$ID = 0;
$sql = "exec Apprasial_Details_Insert 
        @Employee_Name = '".$_POST["Emp_Name"]."',
        @Position = '".$_POST["Position"]."',
        @Dept_Branch = '".$_POST["Branch"]."',
        @Location = '".$_POST["Location"]."',
        @Appraiser_Name_1 = '".$_POST["1Appraiser"]."',
        @Appraiser_Name_2 = '".$_POST["2Appraiser"]."',
        @Title_1 = '".$_POST["1AppraiserTitle"]."',
        @Title_2 = '".$_POST["2AppraiserTitle"]."',
        @Date = '$datetime_formatted',
        @Grand_Total = '".$_POST["GrandTotal"]."',
        @Staff_ID = '".$_POST["Staff_ID"]."'
        @new_identity = $ID";
$procedure_params = array
(
    array(&$ID['new_identity'], SQLSRV_PARAM_OUT,SQLSRV_PHPTYPE_INT)
);      
$stmt = sqlsrv_query( $conn, $sql,$procedure_params);
if( $stmt === false ) 
{
    //header("Location: error.php?id=$statement"); 
    die( print_r( sqlsrv_errors(), true));
}

The error I get :

Array ( [0] => Array ( [0] => 42000 [SQLSTATE] => 42000 1 => 102 [code] => 102 2 => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near '@new_identity'. [message] => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near '@new_identity'. ) )

If I change my code to :

$sql = "exec Apprasial_Details_Insert 
        @Employee_Name = '".$_POST["Emp_Name"]."',
        @Position = '".$_POST["Position"]."',
        @Dept_Branch = '".$_POST["Branch"]."',
        @Location = '".$_POST["Location"]."',
        @Appraiser_Name_1 = '".$_POST["1Appraiser"]."',
        @Appraiser_Name_2 = '".$_POST["2Appraiser"]."',
        @Title_1 = '".$_POST["1AppraiserTitle"]."',
        @Title_2 = '".$_POST["2AppraiserTitle"]."',
        @Date = '$datetime_formatted',
        @Grand_Total = '".$_POST["GrandTotal"]."',
        @Staff_ID = '".$_POST["Staff_ID"]."'
        @new_identity = ?";
$procedure_params = array
(
    array(&$ID['new_identity'], SQLSRV_PARAM_OUT)
);      
$stmt = sqlsrv_query( $conn, $sql,$procedure_params);
if( $stmt === false ) 
{
    //header("Location: error.php?id=$statement"); 
    die( print_r( sqlsrv_errors(), true));
}

The error I get :

Array ( [0] => Array ( [0] => IMSSP [SQLSTATE] => IMSSP 1 => -7 [code] => -7 2 => An invalid PHP type was specified as an output parameter. DateTime objects, NULL values, and streams cannot be specified as output parameters. [message] => An invalid PHP type was specified as an output parameter. DateTime objects, NULL values, and streams cannot be specified as output parameters. ) )

I refered to the posts below :

Link 1 Link 2

Where have I gone wrong? Appreciate any suggestion / help

1 Answer 1

1

You need comma after @Staff_ID = '".$_POST["Staff_ID"]."' so:

$ID = 0;
$sql = "exec Apprasial_Details_Insert 
        @Employee_Name = '".$_POST["Emp_Name"]."',
        @Position = '".$_POST["Position"]."',
        @Dept_Branch = '".$_POST["Branch"]."',
        @Location = '".$_POST["Location"]."',
        @Appraiser_Name_1 = '".$_POST["1Appraiser"]."',
        @Appraiser_Name_2 = '".$_POST["2Appraiser"]."',
        @Title_1 = '".$_POST["1AppraiserTitle"]."',
        @Title_2 = '".$_POST["2AppraiserTitle"]."',
        @Date = '$datetime_formatted',
        @Grand_Total = '".$_POST["GrandTotal"]."',
        @Staff_ID = '".$_POST["Staff_ID"]."',
        @new_identity = $ID";
$procedure_params = array
(
    array(&$ID['new_identity'], SQLSRV_PARAM_OUT,SQLSRV_PHPTYPE_INT)
);      
$stmt = sqlsrv_query( $conn, $sql,$procedure_params);
if( $stmt === false ) 
{
    //header("Location: error.php?id=$statement"); 
    die( print_r( sqlsrv_errors(), true));
}
Sign up to request clarification or add additional context in comments.

1 Comment

Okay, now I feel stupid! thanks for the answer :) Appreciate it :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.