ParamByName with temporary table

[eduardomendes]

When I try to perform,

SELECT * into #Temp_Test FROM dbo.Employee
WHERE Id = 00001 AND ((MONTH(LastDate) <= :pMonth and YEAR(LastDate) = :pYear) or YEAR(pLastDate) < :pYear)


It's drop the temporary table and I can't access it later.

But if I hard code the params on the query, it works fine.

Could you help me?

[AndreyZ]

Hello,

This behaviour is connected with the specificity of SQL Server work with temporary tables. You can use this code:

Code: Select all

  UniQuery.SQL.Clear;
  UniQuery.SQL.Add('SELECT * INTO #Temp_Test FROM dbo.Employee');
  UniQuery.SQL.Add('WHERE Id = 00001 AND ((MONTH(LastDate) <= :pMonth and YEAR(LastDate) = :pYear) or YEAR(pLastDate) < :pYear)');
  UniQuery.SQL.Add('SELECT * FROM #Temp_Test');
  UniQuery.ParamByName('pMonth').AsInteger := 1;
  UniQuery.ParamByName('pYear').AsInteger := 2000;
  UniQuery.Open;

Also you can use global temporary tables.