php - 如果使用参数绑定(bind)，PHP中的查询将无法在SQL Server中查看临时表

以下代码按预期工作(假设变量存在):

$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);
}

但是，如果我将$ select更改为以下内容，它将不起作用:

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

运行第二条语句“无效的对象名称'#mytable_temp”时出现错误。为什么使用参数绑定(bind)会导致临时表不可见？

我知道，如果我在同一个sqlsrv_query()语句中包含两个语句，则可以使它工作，但这不是我的用例的选择。我也知道如果使用全局(## mytable_temp)表，它可以工作，但这也不是一种选择。

我正在运行PHP 5.4.12，并在SQL Server 11.0.3(2012 SP1)和10.50.4000(2008 SP2)上尝试了该代码。

最佳答案

这是我的解释，说明为什么在使用参数的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执行，并且在存储过程内部创建的临时表对该存储过程的调用者不可见。

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

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来调用您的过程。 (我个人更喜欢这种方法)。

显式创建(也可以删除)临时表。

这是我用来验证最后一种变通办法正常工作的代码。已使用PHP 5.4.28，SQL Server Express 2014，用于PHP SQLSRV 3.2的Microsoft驱动程序进行了验证。它使用额外的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脚本

$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";

脚本的输出

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