sqlsrv_query

  • Article

Prepares and executes a statement.

Syntax

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

Parameters

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

$tsql: The Transact-SQL expression that corresponds to the prepared 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 PHP variable.

  • An array with the following structure:

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

    The description for each element of the array is in the table below:

    Element

    Description

    $value

    A literal value, a PHP variable, or a PHP by-reference 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 supported keys are as follows:

Key

Supported Values

Description

QueryTimeout

A positive integer value.

Sets the query timeout in seconds. By default, the driver will wait indefinitely for results.

SendStreamParamsAtExec

true or false

The default value is true.

Configures the driver to send all stream data at execution (true), or to send stream data in chunks (false). By default, the value is set to true. For more information, see sqlsrv_send_stream_data.

Scrollable

SQLSRV_CURSOR_FORWARD

SQLSRV_CURSOR_STATIC

SQLSRV_CURSOR_DYNAMIC

SQLSRV_CURSOR_KEYSET

For more information about these values, see Specifying a Cursor Type and Selecting Rows.

Return Value

A statement resource. If the statement cannot be created and/or executed, false is returned.

Remarks

The sqlsrv_query function is well-suited for one-time queries and should be the default choice to execute queries unless special circumstances apply. This function provides a streamlined method to execute a query with a minimum amount of code. The sqlsrv_query function does both statement preparation and statement execution, and can be used to execute parameterized queries.

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

Example

In the following example, a single row is inserted into 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.

Note

Although the following example uses an INSERT statement to demonstrate the use of sqlsrv_query for a one-time statement execution, the concept applies to any Transact-SQL statement.

<?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 the parameterized query. */
$tsql = "INSERT INTO Sales.SalesOrderDetail 
        (SalesOrderID, 
         OrderQty, 
         ProductID, 
         SpecialOfferID, 
         UnitPrice, 
         UnitPriceDiscount)
        VALUES 
        (?, ?, ?, ?, ?, ?)";

/* Set parameter values. */
$params = array(75123, 5, 741, 1, 818.70, 0.00);

/* Prepare and execute the query. */
$stmt = sqlsrv_query( $conn, $tsql, $params);
if( $stmt )
{
     echo "Row successfully inserted.\n";
}
else
{
     echo "Row insertion failed.\n";
     die( print_r( sqlsrv_errors(), true));
}

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

The example below 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 the parameterized query. */
$tsql = "UPDATE Sales.SalesOrderDetail 
         SET OrderQty = ( ?) 
         WHERE SalesOrderDetailID = ( ?)";

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

/* Execute the query. */
if( sqlsrv_query( $conn, $tsql, $params))
{
      echo "Statement executed.\n";
} 
else
{
      echo "Error in statement execution.\n";
      die( print_r( sqlsrv_errors(), true));
}

/* Free connection resources. */
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

Other Resources

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

Using Directional Parameters