TADOQuery Temp Table如果在查询中有参数，则丢失

在批处理，名称范围和数据库上下文方面， sp_executesql 具有与 EXECUTE 相同的行为.在执行 sp_executesql 语句之前，不会编译 sp_executesql @stmt 参数中的Transact-SQL语句或批处理.然后，将 @stmt 的内容编译并执行，作为与名为 sp_executesql 的批处理的执行计划分开的执行计划. sp_executesql 批处理不能引用在调用 sp_executesql 的批处理中声明的变量. sp_executesql 批处理中的本地游标或变量对于调用 sp_executesql 的批处理不可见.数据库上下文中的更改仅持续到 sp_executesql 语句的末尾.I have a TADOQuery that generates a tempTable if I hard code the "Where parameter, it works fine, but if I use a TADO Parameter the next query doesn't know about the temp table.What am I doing wrong?I wish I could simplify this example but here it is.(SQL Server) CREATE TABLE brFTNode_Children ( pID integer NOT NULL, cID integer NOT NULL, primary key (pID, cID) ); insert into brFTNode_Children values(1,2); insert into brFTNode_Children values(1,3); insert into brFTNode_Children values(3,4); insert into brFTNode_Children values(3,5); insert into brFTNode_Children values(6,4); insert into brFTNode_Children values(6,7);Code (Doesn't work)procedure Foo(fDBCon : TADOConnection);const CreateTempTable = 'WITH FT_CTE AS( ' + 'SELECT pID, cID FROM brFTNode_Children ' + 'WHERE pID = :TOPID ' + 'UNION ALL ' + ' SELECT e.pID, e.cID FROM brFTNode_Children e ' + ' INNER JOIN FT_CTE ftCTE on (ftCTE.cID = e.pID)) ' + 'SELECT * INTO #ParentChild FROM FT_CTE; '; GetSQL = 'SELECT pID, cID FROM #ParentChild ORDER BY pID; ';var q1 : TADOQuery; q2 : TADOQuery;begin q1 := TADOQuery.Create(nil); q1.Connection := fDBCon; q1.SQL.Text := CreateTempTable; q1.ParamCheck := True; q1.Parameters.ParamByName('TOPID').DataType := ftInteger; q1.Parameters.ParamByName('TOPID').Value := 1; q1.ExecSQL; q2 := TADOQuery.Create(nil); q2.Connection := fDBCon; q2.SQL.Text := GetSQL; q2.Active := true; //Fails here does not know table #ParentChildend;Code - Works with the constant in the SQL queryfunction TGenerateSolveFile.GetBinaryStream( topID : Cardinal; var bFile: TMemoryStream): Boolean;const CreateTempTable = 'WITH FT_CTE AS( ' + 'SELECT pID, cID FROM brFTNode_Children ' + 'WHERE pID = 1 ' + //Changed To a constant 'UNION ALL ' + ' SELECT e.pID, e.cID FROM brFTNode_Children e ' + ' INNER JOIN FT_CTE ftCTE on (ftCTE.cID = e.pID)) ' + 'SELECT * INTO #ParentChild FROM FT_CTE; '; GetSQL = 'SELECT pID, cID FROM #ParentChild ORDER BY pID; ';var q1 : TADOQuery; q2 : TADOQuery;begin q1 := TADOQuery.Create(nil); q1.Connection := fDBCon; q1.SQL.Text := CreateTempTable;// q1.ParamCheck := True;// q1.Parameters.ParamByName('TOPID').DataType := ftInteger;// q1.Parameters.ParamByName('TOPID').Value := 1; q1.ExecSQL; q2 := TADOQuery.Create(nil); q2.Connection := fDBCon; q2.SQL.Text := GetSQL; q2.Active := true;end; 解决方案 A parameterized query is using exec sp_executesql, which has it's own session.You will get this from the profiler.exec sp_executesql N'WITH FT_CTE AS( SELECT pID, cID FROM brFTNode_Children WHERE pID = @P1 UNION ALL SELECT e.pID, e.cID FROM brFTNode_Children e INNER JOIN FT_CTE ftCTE on (ftCTE.cID = e.pID)) SELECT * INTO #ParentChild FROM FT_CTE;',N'@P1 int',1If you execute this in the SSMS and call select * from #ParentChild afterwards you will get the same error.sp_executesql (Transact-SQL) sp_executesql has the same behavior as EXECUTE with regard to batches, the scope of names, and database context. The Transact-SQL statement or batch in the sp_executesql @stmt parameter is not compiled until the sp_executesql statement is executed. The contents of @stmt are then compiled and executed as an execution plan separate from the execution plan of the batch that called sp_executesql. The sp_executesql batch cannot reference variables declared in the batch that calls sp_executesql. Local cursors or variables in the sp_executesql batch are not visible to the batch that calls sp_executesql. Changes in database context last only to the end of the sp_executesql statement. 这篇关于TADOQuery Temp Table如果在查询中有参数，则丢失的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！