sqlsrv_query

Microsoft Drivers for PHP for SQL Server

Collapse image Expand Image Copy image CopyHover image

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:

      Copy imageCopy Code
    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.

    For more information about PHP constants, see Constants (Microsoft Drivers for PHP for SQL Server).

    $sqlType[OPTIONAL]

    A SQLSRV_SQLTYPE_* constant that specifies the SQL Server data type of the input value.

    For more information about PHP constants, see Constants (Microsoft Drivers for PHP for SQL Server).

$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

SQLSRV_CURSOR_CLIENT_BUFFERED

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 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.

  Copy imageCopy Code
<?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.

  Copy imageCopy Code
<?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