How to: Execute a Query Multiple Times

Article
10/20/2009

The example in this topic demonstrates how to use the SQL Server Driver for PHP to execute a prepared statement multiple times. By using the combination of sqlsrv_prepare and sqlsrv_execute, a statement is compiled one time on the server and can then be executed multiple times with different parameter values.

Note

For one-time queries, it is recommended that you use sqlsrv_query.

When you prepare a statement that uses variables as parameters, the variables are bound to the statement. That means that if you update the values of the variables, the next time you execute the statement it will run with updated parameter values.

Example

The following example demonstrates how to prepare a statement and then re-execute it with different parameter values. The example updates the OrderQty column of the Sales.SalesOrderDetail table in the AdventureWorks database. After the updates have occurred, the database is queried to verify that the updates were successful. The example assumes that SQL Server and the AdventureWorks database are installed on the local computer. All output is written to the console when the example is run from the command line.

<?php
/* Connect to the local server using Windows Authentication and
specify the AdventureWorks database as the database in use. */
$serverName = "(local)";
$connectionInfo = array( "Database"=>"AdventureWorks");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false )
{
     echo "Could not connect.\n";
     die( print_r( sqlsrv_errors(), true));
}

/* Define the parameterized query. */
$tsql = "UPDATE Sales.SalesOrderDetail
         SET OrderQty = ?
         WHERE SalesOrderDetailID = ?";

/* Initialize parameters and prepare the statement. Variables $qty
and $id are bound to the statement, $stmt1. */
$qty = 0; $id = 0;
$stmt1 = sqlsrv_prepare( $conn, $tsql, array( &$qty, &$id));
if( $stmt1 )
{
     echo "Statement 1 prepared.\n";
} 
else 
{
     echo "Error in statement preparation.\n";
     die( print_r( sqlsrv_errors(), true));
}

/* Set up the SalesOrderDetailID and OrderQty information. This array
maps the order ID to order quantity in key=>value pairs. */
$orders = array( 1=>10, 2=>20, 3=>30);

/* Execute the statement for each order. */
foreach( $orders as $id => $qty)
{
     // Because $id and $qty are bound to $stmt1, their updated
     // values are used with each execution of the statement. 
     if( sqlsrv_execute( $stmt1) === false )
     {
          echo "Error in statement execution.\n";
          die( print_r( sqlsrv_errors(), true));
     }
}
echo "Orders updated.\n";

/* Free $stmt1 resources.  This allows $id and $qty to be bound to a different statement.*/
sqlsrv_free_stmt( $stmt1);

/* Now verify that the results were successfully written by selecting 
the newly inserted rows. */
$tsql = "SELECT OrderQty 
         FROM Sales.SalesOrderDetail 
         WHERE SalesOrderDetailID = ?";

/* Prepare the statement. Variable $id is bound to $stmt2. */
$stmt2 = sqlsrv_prepare( $conn, $tsql, array( &$id));
if( $stmt2 )
{
     echo "Statement 2 prepared.\n";
} 
else 
{
     echo "Error in statement preparation.\n";
     die( print_r( sqlsrv_errors(), true));
}

/* Execute the statement for each order. */
foreach( array_keys($orders) as $id)
{
     /* Because $id is bound to $stmt2, its updated value 
        is used with each execution of the statement. */
     if( sqlsrv_execute( $stmt2))
     {
          sqlsrv_fetch( $stmt2);
          $quantity = sqlsrv_get_field( $stmt2, 0);
          echo "Order $id is for $quantity units.\n";
     }
     else
     {
          echo "Error in statement execution.\n";
          die( print_r( sqlsrv_errors(), true));
     }
}

/* Free $stmt2 and connection resources. */
sqlsrv_free_stmt( $stmt2);
sqlsrv_close( $conn);
?>

For alternative strategies for writing and reading large amounts of information, see Batches of SQL Statements and BULK INSERT.

How to: Execute a Query Multiple Times

Example

See Also

Tasks

Other Resources

Additional resources