drop table with temporary table giving error

Question

drop table with temporary table giving error

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (0 votes)

2

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

Delphi returns the following message at runtime:

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

If anyone can help me, I'm very grateful.

EDIT: Test with the suggestion of the 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 also did not work. Now the error is compile. delphi returns me:

Missing operator or semicolon. Statement expected, but expression of type 'String' found.

Do you have another idea?

sql sql-server delphi

asked by anonymous 20.06.2018 / 18:15

3 answers

page with search get is not showing because of REQUEST_URI Injected Code

score 1 · 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 the single quotation marks with another single quotation mark ' :

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

score 0 · Answer 2

This if exists fault syntax may not work in SQL-Server , it's a new syntax from SQL SERVER 2016 , you can do this:

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 IS NOT NULL

score 0 · Answer 3

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

-- 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 pay attention to the apostrophes:

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