Sql-server – Using WITH and IF in the same query

sql serversql-server-2012

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.

Best Answer

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

Related Question