下面是我解释为什么在使用参数的查询之后看不到临时表。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最终会调用（最有可能的是，尽管我没有跟踪它）。您将获得此记录的行为 - 作为查询的一部分，在此存储过程中创建一个临时表，并在返回时立即删除。运行不带参数的 SQL 语句时，PHP 按原样将其发送到服务器，而不使用 。sp_executesqlsp_executesqlsp_executesql
我想到的解决方法很少。
以下是我用于验证最后一种解决方法是否有效的代码。已通过 PHP 5.4.28、SQL Server Express 2014、Microsoft 驱动程序 （适用于 PHP SQLSRV 3.2） 进行验证。它使用额外的语句显式创建临时表，然后使用而不是单个语句。CREATE TABLEINSERT INTOSELECT 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
