drop table with temporary table giving error

Question

qryCIDtemp.SQL.Text := 'drop table if exists #tempCID';  //apaga a tabelas temporária

Delphi returns me the following message at runtime:

Invalid use of keyword
Token: if
Line Number: 1.

If anyone can help me, I’d be grateful.

EDIT: Test with the suggested answer below:
I did as you suggested:

 qryCIDtemp.SQL.Text := 'if object_id('#tempCID','U') Is not null drop table #tempCID'; //apaga a tabelas temporária

and it didn’t work either. Now the error is compilation. Delphi returns me:

Missing Operator or semicolon. Statement expected, but Expression of type 'String' found.

Have another idea?

Answer 1

You need to use the command:

IF OBJECT_ID('#tempCID') IS NOT NULL DROP TABLE #tempCID

However, for this to work in Delphi, you need to use the following String, escaping single quotes with another single quote ':

 qryCIDtemp.SQL.Text := 'IF OBJECT_ID(''#tempCID'') IS NOT NULL DROP TABLE #tempCID';

Answer 2

That syntax if exists may not work on SQL-Server, is a new syntax from SQL SERVER 2016, you can do so:

IF OBJECT_ID('#tempCID', 'U') IS NOT NULL DROP TABLE #tempCID

OBJECT_ID is a function that makes the id of an object, and if its return is null, the object (table in case) does not exist, so the IS NOT NULL

Answer 3

In the OBJECT_ID function, for temporary tables it is always necessary to cite the name of the database tempDB:

-- código #1
IF Object_ID(N'tempDB..#tempCID', N'U') is not null
  DROP TABLE #tempCID;

When encapsulating the above command in the program in Delphi watch the apostrophes:

qryCIDtemp.SQL.Text := 'IF Object_ID(N''tempDB..#tempCID'', N''U'') is not null DROP TABLE #tempCID';