TADOQuery Temp Table Lost if it has a parameter in query

Question

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 #ParentChild
end;

Code - Works with the constant in the 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;

Why do you need to execute 2 queries? have you tried simply `q2.SQL.Text := CreateTempTable + GetSQL;` in any case I would create a single SP for this. passing the parameters to it and get back the recordset. — kobik, Oct 24 '14 at 16:14

score 4 · Accepted Answer · edited Oct 25 '14 at 09:50

4

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',1

If 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.

edited Oct 25 '14 at 09:50

Sir Rufo

18,395
2
39
73

answered Oct 24 '14 at 15:29

bummi

27,123
14
62
101

Is there a way for the other query to use the same session. I don't want to have to run the temp query for each query that needs it, and I cant create a real table because there are multiple threads doing the same code. Or is there some other solution. – runfastman Oct 24 '14 at 15:42
the session is lost after sp_executesql finished, you might use global temporary tables with names built of an adapted GUID to avoid collisions with other users. These will be dropped after the last user disconnected. Something like ##F7BBE50B_9406_4A35_9FAD_6C54D317A390. BTW: nice prepared question. :) – bummi Oct 24 '14 at 15:51
I found a solution, and feel a little sheepish. I will just replace ID in the string before executing the query. Simple solution. – runfastman Oct 24 '14 at 16:16
@bummi, I don't think there is a need to explicit a GUID using ##table. SQL server will do that for you. – kobik Oct 24 '14 at 16:22
@kobik not sure if we are thinking of the same thing: [Local and global temporary tables in SQL Server](http://stackoverflow.com/questions/2920836/local-and-global-temporary-tables-in-sql-server) – bummi Oct 24 '14 at 16:34
@bummi, cool. `CREATE TABLE ##t` will create a global temp table. the table itself will have a GUID already. and as you mentioned: "These will be dropped after the last user disconnect". I might have misunderstand you. What I meant is, you don't need to explicitly specify a GUID yourself. +1 – kobik Oct 24 '14 at 16:39

TADOQuery Temp Table Lost if it has a parameter in query

1 Answers1

Linked