5

I'm trying to use the WITH clause and IF statement, but it does not seem to work and I cannot find any reason why it would not. Syntax should be correct and still it is complaining about the syntax.

The error I receive is "Incorrect syntax near ';'. I have tried removing ; characters, but then it says "Incorrect syntax near the keyword 'IF'.

Is it even possible to use the WITH and IF syntax like this? The query does work without the IF statements (with only one of the select queries).

With measurements as 
        (
        (SELECT * from measurement_database1)
        UNION ALL
        (SELECT * from measurement_database2)
        UNION ALL
        (SELECT * from measurement_database3)
        );

    IF @device= 'en1' AND @other_parameter= 'yes'
        (
        select * from measurements where device like 'en1' and op like 'yes'
            );
    IF @device= 'en2' AND @other_parameter= 'no'
        (
        select * from measurements where device like 'en2' and op like 'no'
            );
    IF @device= 'en3' AND @other_parameter= 'yes'
        (
        select * from measurements where device like 'en3' and op like 'yes'
            );

I'm running Microsoft SQL Server 2012 11.0.6607.3.

  • IF is a T-SQL statement, but not part of SQL. – Colin 't Hart Oct 17 '17 at 10:01
15

It may help you more if you think about it, not as a WITH statement but as a Common Table Expression (CTE).

That way you can go back to the Microsoft Documents on CTEs and read the following line;

A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE statement that references some or all the CTE columns. A CTE can also be specified in a CREATE VIEW statement as part of the defining SELECT statement of the view.

Although technically you can define a CTE and then not reference it at all but then what would be the point in defining it? e.g.

WITH Missing AS (SELECT 'The Point' AS What)
SELECT * FROM sys.messages;

Going back to your case, you could rewrite it using CASE statements instead of IF statements but again what's the point? You don't need them.

You're query can be re-written to:

DECLARE @device char(3) = 'en3',
        @other_parameter varchar(3) = 'yes';
WITH measurements AS (
                         (SELECT    *
                          FROM  measurement_database1)
                         UNION ALL
                         (SELECT    *
                          FROM  measurement_database2)
                         UNION ALL
                         (SELECT    *
                          FROM  measurement_database3)
                     )
SELECT  *
FROM    measurements
WHERE   device LIKE @device
  AND   op LIKE @other_parameter;

So there's no need of IF statements at all! HTH

  • 5
    Ah, yes. My first reaction whenever I see an IF in SQL is pretty much choking. My second reaction is typically to rip it out. Very, very rarely are IF statements necessarily in proper set-based SQL. – a CVn Oct 16 '17 at 13:12
  • 1
    Just as an exception WITH Missing AS (SELECT 'The Point' AS What) SELECT 'Foo' will give you an error Common table expression defined but not used. – Martin Smith Oct 16 '17 at 17:54
  • @MartinSmith The difference being you dropped the FROM sys.messages;, right? You can have unused tables you just need to use a table at least – Brad Oct 16 '17 at 18:06
  • 1
    @MartinSmith oh that is just weird. What's the point of the Common table expression defined but not used. message really then? what problem is it solving if it's so easily circumvented? – Brad Oct 16 '17 at 18:19
  • 2
    @Brad - No idea - I assume it may just be a simple check that happens at parse time. – Martin Smith Oct 16 '17 at 18:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.