Skip to main content
1-Visitor
February 7, 2020
Question

SQL Server SELECT IF Exists Not working

  • February 7, 2020
  • 2 replies
  • 5260 views

Hi,

Any idea about why SELECT IF EXISTS command is not working.

mnaeem_0-1581071471271.png

Thank You!

Naeem

2 replies

17-Peridot
February 7, 2020

Hi,

 

Syntax is not correct. Please check this link: https://stackoverflow.com/questions/7887011/how-to-drop-a-table-if-it-exists

 

Hope it helps,

Raluca Edu

18-Opal
February 7, 2020

Hello Naeem,

 

You can't use [[abc]] as placeholders for table names, only for parameter values. It's a rather frequent question, here's another example: https://community.ptc.com/t5/ThingWorx-Developers/Problems-with-the-input-parameter-in-the-SQL-query-service/m-p/618832

 

Regards,
Constantine

18-Opal
February 7, 2020

Just to be precise -- it is technically possible to use <<abc>>, which simply replaces strings in your SQL statement, so you can use it anywhere in your service (unlike [[abc]], which works as PreparedStatement parameters). However, this makes your service vulnerable to SQL Injection exploits (e.g. https://portswigger.net/web-security/sql-injection), so you should be very (VERY) careful with that. Make sure you only expose this service to System user and wrap it in another one, which thoroughly validates its parameters. If you don't do it -- somebody might be able to steal your credentials, drop the entire database, etc. etc.

 

/ Constantine

mnaeem1-VisitorAuthor
1-Visitor
February 11, 2020

Thank You @Constantine and @raluca_edu ,

Still not able to drop the temporary table according to the link that @raluca_edu mentioned in her comment.

mnaeem_0-1581411860226.png

 

Moreover, according to @Constantine I'd like to avoid using query as a string.

 

Basically I've a query in which I'm using both DDL and DML functionality. Is there a way of implementing it.

I'd like to use the following single query which works fine, not in thingworx.

 

IF OBJECT_ID('tempdb..#RESULT_TABLE') IS NOT NULL DROP TABLE #RESULT_TABLE;

WITH T1 As
(
SELECT ............
WHERE ...............
AND ................
AND ...................
)
SELECT DISTINCT ..........
INTO #RESULT_TABLE
FROM .................
WHERE ..............
AND ....................
AND ..................;


WITH TO_DELETE AS
(
SELECT ...................
)
DELETE T1
FROM #RESULT_TABLE T1
INNER JOIN TO_DELETE T2 ON T1.ID  = T2.ID;

 

WITH valid AS
(
SELECT DISTINCT .............. FROM #RESULT_TABLE
)
SELECT SUM(Duration) tot_duration FROM valid;

DROP TABLE #RESULT_TABLE

 

Thank You!

Naeem