php数据库查询 临时表,如果使用参数绑定,PHP中的查询将无法在SQL Server中查看临时表...

这是我为什么在使用参数的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在嵌套存储过程的范围内执行，并且存储过程中创建的临时表对此存储过程的调用者是不可见的。

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.

当准备一个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