TADOQuery Temp Table Lost if it has a parameter in the query
I have a TADOQuery that generates a tempTable, if I hardcode the Where parameter it works fine, but if I use the TADO parameter the next query doesn't know about the temp table.
What am I doing wrong?
I would like to 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 #ParentChild end;
Code - works with constant in SQL query
function 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;
source to share
In a parameterized query, it is used exec sp_executesql
that has its 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',1
If you run this in SSMS and call it select * from #ParentChild
, you get the same error.
sp_executesql
has the same behaviorEXECUTE
as for batches, namespace and database context. The Transact-SQL operation or package in the parameter issp_executesql
@stmt
not compiled until the statement is executedsp_executesql
. The content is@stmt
then compiled and executed as an execution plan separate from the package's execution plan, which is calledsp_executesql
. A packagesp_executesql
cannot refer to variables declared in the package that callssp_executesql
. Local cursors or variables in a packagesp_executesql
do not appear in the package that callssp_executesql
. Changes in the context of the database persist only until the end of the statementsp_executesql
.
source to share