Query in PHP fails to see temp table in SQL Server if parameter binding is used

Question

The following code works as expected (assuming the variables exist):

$connectionInfo = ['Database'=>$dbName, 'UID'=>$username, 'PWD'=>$pwd, 'ReturnDatesAsStrings'=>true, 'CharacterSet'=>'UTF-8'];
$conn           = sqlsrv_connect($server, $connectionInfo);

$select  = sqlsrv_query($conn, 'SELECT * INTO #mytable_temp FROM mytable WHERE myfield = \'myvalue\'', []);
$select2 = sqlsrv_query($conn, 'SELECT * FROM #mytable_temp ', []);

if (!$select2) {
    $errors = sqlsrv_errors();
    var_dump($errors);
} else {
    $res = sqlsrv_fetch_array($select2, SQLSRV_FETCH_ASSOC);
    var_dump($res);
}

However, if I change $select to the following, it doesn't work:

$select  = sqlsrv_query($conn, 'SELECT * INTO #mytable_temp FROM mytable WHERE myfield = ?', ['myvalue']);

I get an error when running the second statement that says "Invalid object name '#mytable_temp". Why would using parameter binding cause the temp table to not be visible?

I am aware that I could get it working if I include both statements in the same sqlsrv_query() statement, but that's not an option for my use case. I also do know it works if using a global (##mytable_temp) table, but that's not an option either.

I'm running PHP 5.4.12 and tried the code on SQL Server 11.0.3 (2012 SP1) and 10.50.4000 (2008 SP2).

Answer 1

To complement Vladimir's answer, here are the trace results (PHP 5.6.9, MSSQL 2014 Express).

When you don't add any parameters

$select  = sqlsrv_query($conn, 'SELECT * INTO #mytable_temp FROM mytable WHERE myfield = \'myvalue\'', []);
$select2 = sqlsrv_query($conn, 'SELECT * FROM #mytable_temp ', []);

PHP will send plain commands to MSSQL:

SELECT * INTO #mytable_temp FROM mytable WHERE myfield = 'myvalue'
SELECT * FROM #mytable_temp 

When you add parameters

$select  = sqlsrv_query($conn, 'SELECT * INTO #mytable_temp FROM mytable WHERE myfield = ?', ['myvalue']);
$select2 = sqlsrv_query($conn, 'SELECT * FROM #mytable_temp ', []);

PHP will then use sp_executesql:

exec sp_executesql N'SELECT * INTO #mytable_temp FROM mytable WHERE myfield = @P1',N'@P1 nvarchar(6)',N'myvalue'
SELECT * FROM #mytable_temp 

Answer 2

Here is my explanation of why the temp table is not seen after the SELECT INTO query that uses parameters.

Consider this T-SQL code (using MyTable created and populated as shown below):

DECLARE @stmt nvarchar(max) = 'SELECT * INTO #mytable_temp FROM mytable WHERE myfield = @P1';
EXECUTE sp_executesql @stmt, N'@P1 varchar(50)', @P1 = 'Value1';

If you run it in SSMS it runs fine and the output in the Messages window says:

(2 row(s) affected)

Try to add one line to the above code in the same SSMS window and run the whole batch:

DECLARE @stmt nvarchar(max) = 'SELECT * INTO #mytable_temp FROM mytable WHERE myfield = @P1';
EXECUTE sp_executesql @stmt, N'@P1 varchar(50)', @P1 = 'Value1';
SELECT * FROM #mytable_temp;

The output is:

(2 row(s) affected)
Msg 208, Level 16, State 0, Line 3
Invalid object name '#mytable_temp'.

The reason is that statement with parameters is executed by sp_executesql in the scope of the nested stored procedure and temporary tables created inside stored procedure are not visible to the caller of this stored procedure.

Execute sp_executeSql for select...into #table but Can't Select out Temp Table Data

https://msdn.microsoft.com/en-us/library/ms174979.aspx

A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process that called the stored procedure that created the table.

When you prepare an SQL statement with parameters PHP eventually calls sp_executesql (most likely, though I didn't trace it). And you get this documented behavior - a temp table is created inside this stored procedure as part of the query and is immediately dropped when sp_executesql returns. When you run an SQL statement without parameters PHP sends it to the server as is without using sp_executesql.

---

There are few workarounds that come to mind.

• Put several SQL statements into one long string and run it using one call to sqlsrv_query.

• Make a stored procedure with parameters and put several SQL statements in it, then call your procedure with a single call to sqlsrv_query. (I personally prefer this approach).

• Create (and optionally drop) temp table explicitly.

Here is the code that I used to verify that the last workaround works. Verified with PHP 5.4.28, SQL Server Express 2014, Microsoft driver for PHP SQLSRV 3.2. It creates temporary table explicitly using extra CREATE TABLE statement and then uses INSERT INTO instead of single SELECT INTO statement.

Create test table and populate with some data

CREATE TABLE [dbo].[MyTable](
    [ID] [int] NOT NULL,
    [MyField] [varchar](50) NOT NULL,
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
))

INSERT INTO [dbo].[MyTable] ([ID],[MyField]) VALUES
(1, 'Value1'),
(2, 'Value2'),
(3, 'Value3'),
(4, 'Value1')

Run php script

$connectionInfo = array("Database" => "tempdb");
$conn = sqlsrv_connect($serverName, $connectionInfo);

if ($conn)
{
     echo "Connection established.\n";
}
else
{
     echo "Connection could not be established.\n";
     die( print_r( sqlsrv_errors(), true));
}

echo "Running CREATE TABLE ...\n";
$sql_create = "CREATE TABLE #mytable_temp([ID] [int] NOT NULL, [MyField] [varchar](50) NOT NULL)";
$stmt_create = sqlsrv_query($conn, $sql_create);
if( $stmt_create === false )
{
    echo "CREATE TABLE failed\n";
    die( print_r( sqlsrv_errors(), true));
}
else
{
    echo "CREATE TABLE result set:\n";
    while ($row = sqlsrv_fetch_array($stmt_create))
    {
        var_dump($row);
    }
}
sqlsrv_free_stmt($stmt_create);


echo "Running INSERT INTO with param ...\n";
$select_into = "INSERT INTO #mytable_temp(ID, MyField) SELECT ID, MyField FROM MyTable WHERE MyField = ?";

$search = "Value1";
$params = array
    (
    array(&$search, SQLSRV_PARAM_IN)
    );
$stmt_into = sqlsrv_query($conn, $select_into, $params);
if( $stmt_into === false )
{
    echo "INSERT INTO failed\n";
    die( print_r( sqlsrv_errors(), true));
}
else
{
    echo "INSERT INTO result set:\n";
    while ($row = sqlsrv_fetch_array($stmt_into))
    {
        var_dump($row);
    }
}
sqlsrv_free_stmt($stmt_into);


echo "Running SELECT FROM ...\n";
$select_from = "SELECT * FROM #mytable_temp";
$stmt_from = sqlsrv_query($conn, $select_from);
if( $stmt_from === false )
{
    echo "SELECT FROM failed\n";
    die( print_r( sqlsrv_errors(), true));
}
else
{
    echo "SELECT FROM result set:\n";
    while ($row = sqlsrv_fetch_array($stmt_from))
    {
        var_dump($row);
    }
}

echo "end\n";

Output of the script

Connection established.
Running CREATE TABLE ...
CREATE TABLE result set:
Running INSERT INTO with param ...
INSERT INTO result set:
Running SELECT FROM ...
SELECT FROM result set:
array(4) {
  [0]=>
  int(1)
  ["ID"]=>
  int(1)
  [1]=>
  string(6) "Value1"
  ["MyField"]=>
  string(6) "Value1"
}
array(4) {
  [0]=>
  int(4)
  ["ID"]=>
  int(4)
  [1]=>
  string(6) "Value1"
  ["MyField"]=>
  string(6) "Value1"
}
end