How to: Specify Parameter Direction Using the SQLSRV Driver

Microsoft Drivers for PHP for SQL Server

Collapse image Expand Image Copy image CopyHover image

This topic describes how to use the SQLSRV driver to specify parameter direction when you call a stored procedure. Note that the parameter direction is specified when you construct a parameter array (step 3) that is passed to sqlsrv_query or sqlsrv_prepare.

To specify parameter direction

  1. Define a Transact-SQL query that calls a stored procedure. Use question marks (?) instead of the parameters to be passed to the stored procedure. For example, this string calls a stored procedure (UpdateVacationHours) that accepts two parameters:

      Copy imageCopy Code
    $tsql = "{call UpdateVacationHours(?, ?)}";
    Note Note

    Calling stored procedures using canonical syntax is the recommended practice. For more information about canonical syntax, see Calling a Stored Procedure.

  2. Initialize or update PHP variables that correspond to the placeholders in the Transact-SQL query. For example, the following code initializes the two parameters for the UpdateVacationHours stored procedure:

      Copy imageCopy Code
    $employeeId = 101;
    $usedVacationHours = 8;
    Note Note

    Variables that are initialized or updated to null, DateTime, or stream types cannot be used as output parameters.

  3. Use your PHP variables from step 2 to create or update an array of parameter values that correspond, in order, to the parameter placeholders in the Transact-SQL string. Specify the direction for each parameter in the array. The direction of each parameter is determined in one of two ways: by default (for input parameters) or by using SQLSRV_PARAM_* constants (for output and bidirectional parameters). For example, the following code specifies the $employeeId parameter as an input parameter and the $usedVacationHours parameter as a bidirectional parameter:

      Copy imageCopy Code
    $params = array(
                     array($employeeId, SQLSRV_PARAM_IN),
                     array($usedVacationHours, SQLSRV_PARAM_INOUT)
                    );

    To understand the syntax for specifying parameter direction in general, suppose that $var1, $var2, and $var3 correspond to input, output, and bidirectional parameters, respectively. You can specify the parameter direction in either of the following ways:

    • Implicitly specificy the input parameter, explicitly specify the output parameter, and explicitly specify a bidirectional parameter:

        Copy imageCopy Code
      array( 
             array($var1),
             array($var2, SQLSRV_PARAM_OUT),
             array($var3, SQLSRV_PARAM_INOUT)
             );
    • Explicitly specificy the input parameter, explicitly specificy the output parameter, and explicitly specificy a bidirectional parameter:

        Copy imageCopy Code
      array( 
             array($var1, SQLSRV_PARAM_IN),
             array($var2, SQLSRV_PARAM_OUT),
             array($var3, SQLSRV_PARAM_INOUT)
             );
  4. Execute the query with sqlsrv_query or with sqlsrv_prepare and sqlsrv_execute. For example, the following code uses the connection $conn to execute the query $tsql with parameter values specified in $params:

      Copy imageCopy Code
    sqlsrv_query($conn, $tsql, $params);

See Also