How to Execute a Stored Procedure in PHP Using SQLsrv and "" Style Parameters

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

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

php - how execute a sqlsrv stored procedure

A possible approach here is to parameterize the statement and use sqlsrv_query(). As is mentioned in the documentation, the sqlsrv_query function is well-suited for one-time queries and should be the default choice to execute queries unless special circumstances apply and sqlsrv_query function does both statement preparation and statement execution, and can be used to execute parameterized queries.

The stored procedure has varchar parameters (and I'm almost sure, that you are using a cyrillic collation), so you may need to use the appropriate encoding ("CharacterSet" => "UTF-8" or "CharacterSet" => SQLSRV_ENC_CHAR in the connection options) and/or character set conversion on the parameters values (with iconv() for example). Reading UTF-8 all the way through is a good starting point.

If the stored procedure returns data, you may try to use sqlsrv_fetch_array() to retrieve the returned data. You may also use SET NOCOUNT ON to prevent SQL Server from passing the count of the affected rows as part of the result set.

The following example, based on your code, is a possible solution to your problem:

<?php
// Connection
$server   = "SERVERNAME";
$database = "DBNAME";
$cinfo = array(
    "CharacterSet" => "UTF-8",
    "Database" => $database
);
$con = sqlsrv_connect($server, $cinfo);
if ($con === false) {
    echo "Error (sqlsrv_connect): ".print_r(sqlsrv_errors(), true);
    exit;
}

// Statement
$sql = "
    SET NOCOUNT ON;
    DECLARE @dt datetime;
    SET @dt = GETDATE();
    EXEC oik..SrezLTGES 
        @Cat = ?, 
        @Ids = ?, 
        @Time = @dt,
        @TimeIsSummer = 1,
        @ShowSystemTime = 1
";      
$params = array("Ë", "140539,140540,140589,150395,180395,180396,180445");
$stmt = sqlsrv_query($con, $sql, $params);
if ($stmt === false) {
    echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
    exit;
}

// Data
while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
   echo print_r($row, true);
}

// End
sqlsrv_free_stmt($stmt);
sqlsrv_close($con);
?>

sqlsrv_execute Errors With PHP Errors and Stored Procedures

First, the stored procedures are correct. One issue is the different declaration for the @provider parameter (@provider nvarchar(64) and @provider nvarchar(256)), but it's probably a typing error.

So, I think that you should make some changes in your script:

• Execute the stored procedures with {call sp_name (?, ...)} syntax. This is a possible reason for the Warning: sqlsrv_execute() expects parameter 1 to be resource, bool given in ajax_functions1.live.php on line ... error, because function sqlsrv_prepare() is not executed correctly and you need to check the result from this execution. Note, that you may use sqlsrv_query(), because this function does both statement preparation and statement execution, and can be used to execute parameterized queries.
• Change the parameters declaration for the db_add_user() function.
• Always check for errors.
• The result from sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC) is an associative array, so $row[0]['sub_unique_id'] is an error.

The following script (based on your code) is a possible solution to your problem:

<?php
function db_add_user($email, $provider, $subuid){
    include 'config.php';

    $params = array(
        array($email, SQLSRV_PARAM_IN),
        array($provider, SQLSRV_PARAM_IN)
    );

    $uid = null;

    $sql = "{call [dbo].[getSubUniqueID_HPCreate]( ?, ? )}"
    $stmt = sqlsrv_query($conn, $sql, $params);
    if ($stmt === false) {
        echo print_r(sqlsrv_errors(), true);
        return null;
    }   

    if ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
        $uid = $row['sub_unique_id'];
    } else {
        $params = array(
            array($email, SQLSRV_PARAM_IN),
            array($provider, SQLSRV_PARAM_IN),
            array($subid SQLSRV_PARAM_IN)
        );
        $sql = "{call [dbo].[insertSubUniqueID_HPCreate]( ?, ?, ? )}"
        $stmt = sqlsrv_query($conn, $sql, $params);
        if ($stmt === false) {
            echo print_r(sqlsrv_errors(), true);
            return null;
        }   
    }

    return $uid;
}
?>

How to return OUT parameters from stored procedure in Symfony 1.4 using Doctrine

Solution:

$sql = "CALL procedure_name('$val1', ... , @out1, @out2, @out3)";
$con = Doctrine_Manager::getInstance()->getCurrentConnection();
$statement = $con->prepare($sql);
$statement->execute();
$statement->closeCursor();
$result = $con->fetchAssoc("SELECT @out1, @out2, @out3");

---

Related Topics