Запрос в PHP не видит временную таблицу в SQL Server, если используется привязка параметров

Ответ 1

Вот мое объяснение, почему временная таблица не видна после запроса SELECT INTO, который использует параметры.

Рассмотрим этот код T-SQL (используя MyTable, созданный и заполненный, как показано ниже):

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

Если вы запустите его в SSMS, он работает нормально, и в окне "Сообщения" выводится сообщение:

(2 row(s) affected)

Попробуйте добавить одну строку к вышеуказанному коду в том же окне SSMS и запустить всю партию:

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;

Вывод:

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

Причина в том, что оператор с параметрами выполняется sp_executesql в области вложенной хранимой процедуры, а временные таблицы, созданные внутри хранимой процедуры, не отображаются вызывающей стороне этой хранимой процедуры.

Выполнить sp_executeSql для выбора... в #table, но не можете выбрать данные таблицы темпа

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

Локальная временная таблица, созданная в хранимой процедуре, удаляется автоматически, когда хранимая процедура завершена. Таблица может быть на которые ссылаются любые вложенные хранимые процедуры, выполняемые сохраненными процедура, которая создала таблицу. На таблицу нельзя ссылаться который вызвал хранимую процедуру, которая создала таблицу.

При подготовке инструкции SQL с параметрами PHP в конечном итоге вызывает sp_executesql (скорее всего, хотя я ее не проследил). И вы получаете это документированное поведение - временная таблица создается внутри этой хранимой процедуры как часть запроса и сразу же отбрасывается при возврате sp_executesql. Когда вы запускаете инструкцию SQL без параметров, PHP отправляет ее на сервер, так как без использования sp_executesql.

Существует несколько обходных решений, которые приходят на ум.

• Поместите несколько операторов SQL в одну длинную строку и запустите ее, используя один вызов sqlsrv_query.

• Сделайте хранимую процедуру с параметрами и поместите в нее несколько операторов SQL, затем вызовите свою процедуру с помощью одного вызова sqlsrv_query. (Я лично предпочитаю этот подход).

• Создать (и необязательно удалить) таблицу temp явно.

Вот код, который я использовал, чтобы проверить, работает ли последний обходной путь. Проверено с помощью PHP 5.4.28, SQL Server Express 2014, драйвер Microsoft для PHP SQLSRV 3.2. Он создает временную таблицу явно, используя дополнительный оператор CREATE TABLE, а затем использует INSERT INTO вместо одиночного оператора SELECT INTO.

Создать тестовую таблицу и заполнить некоторые данные

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')

Запустить 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";

Вывод 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

Ответ 2

Чтобы дополнить ответ Владимира, вот результаты трассировки (PHP 5.6.9, MSSQL 2014 Express).

Если вы не добавите какие-либо параметры

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

PHP отправит простые команды в MSSQL:

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

При добавлении параметров

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

Затем PHP будет использовать 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