Skip to main content
M
mnaeem1-Visitor
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

R
raluca_edu17-Peridot
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

C
Constantine18-Opal
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

C
Constantine18-Opal
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

    M
    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

    Terms & ConditionsCookie settingsAccessibility statement