sqlsrv_prepare

Creates a statement resource associated with the specified connection. This function is useful for execution of multiple queries.

Syntax

sqlsrv_prepare( resource $conn, string $tsql [, array $params [, array $options]])

Parameters

$conn: The connection resource associated with the created statement.

$tsql: The Transact-SQL expression that corresponds to the created statement.

$params [OPTIONAL]: An array of values that correspond to parameters in a parameterized query. Each element of the array can be one of the following:

• A literal value.

• A reference to a PHP variable.

• An array with the following structure:

  array(&$value [, $direction [, $phpType [, $sqlType]]])
  

  Note

  Variables passed as query parameters should be passed by reference instead of by value. For example, pass &$myVariable instead of $myVariable. A PHP warning will be raised when a query with by-value parameters is executed.

  The following table describes these array elements:

  Element	Description
  &$value	A literal value or a reference to a PHP variable.
  $direction[OPTIONAL]	One of the following SQLSRV_PARAM_* constants used to indicate the parameter direction: SQLSRV_PARAM_IN, SQLSRV_PARAM_OUT, SQLSRV_PARAM_INOUT. The default value is SQLSRV_PARAM_IN. For more information about PHP constants, see Constants (Microsoft Drivers for PHP for SQL Server).
  $phpType[OPTIONAL]	A SQLSRV_PHPTYPE_* constant that specifies PHP data type of the returned value.
  $sqlType[OPTIONAL]	A SQLSRV_SQLTYPE_* constant that specifies the SQL Server data type of the input value.

$options [OPTIONAL]: An associative array that sets query properties. The table below lists the supported keys and corresponding values:

Return Value

A statement resource. If the statement resource cannot be created, false is returned.

Remarks

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.

The combination of sqlsrv_prepare and sqlsrv_execute separates statement preparation and statement execution in to two function calls and can be used to execute parameterized queries. This function is ideal to execute a statement multiple times with different parameter values for each execution.

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

For more information, see How to: Retrieve Output Parameters Using the SQLSRV Driver.

Example

The following example prepares and executes a statement. The statement, when executed (see sqlsrv_execute), updates a field in the Sales.SalesOrderDetail table of the AdventureWorks database. 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));
}

/* Set up Transact-SQL query. */
$tsql = "UPDATE Sales.SalesOrderDetail 
         SET OrderQty = ? 
         WHERE SalesOrderDetailID = ?";

/* Assign parameter values. */
$param1 = 5;
$param2 = 10;
$params = array( &$param1, &$param2);

/* Prepare the statement. */
if( $stmt = sqlsrv_prepare( $conn, $tsql, $params))
{
      echo "Statement prepared.\n";
} 
else
{
      echo "Statement could not be prepared.\n";
      die( print_r( sqlsrv_errors(), true));
}

/* Execute the statement. */
if( sqlsrv_execute( $stmt))
{
      echo "Statement executed.\n";
}
else
{
      echo "Statement could not be executed.\n";
      die( print_r( sqlsrv_errors(), true));
}

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

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);
?>

See Also

Tasks

How to: Perform Parameterized Queries

How to: Send Data as a Stream

Concepts

About Code Examples in the Documentation

Retrieving Data

Other Resources

SQLSRV Driver API Reference (Microsoft Drivers for PHP for SQL Server)

Using Directional Parameters

Updating Data (Microsoft Drivers for PHP for SQL Server)