Different SELECT's for an INSERT INTO

Question

I'm trying to have an IF block after an INSERT INTO statement and depending on some conditions, execute a different SELECT that will feed the INSERT INTO.

Here's a (failing) example of what I'm trying to do:

INSERT INTO #TempTable (COL1, COL2, COL3)
    IF @VAR = 'YES'
        BEGIN
            SELECT * FROM TABLE
        END
    ELSE
        BEGIN
            SELECT * FROM TABLE WHERE REGDATE <= '2015-06-12'
        END

But I always end up with this when trying to save the stored proc.

Incorrect syntax near the keyword 'IF'

I thought of building a string and using sp_executesql but I think my initial approach would be less prone to error.

Answer 1

Another option would be to use a CASE statement or IF statement. Maybe this? It is pretty close to what you had:

CREATE TABLE TABLE1 (a INT, b INT, c INT);
CREATE TABLE TABLE2 (a INT, b INT, c INT);
CREATE TABLE TABLE3 (a INT, b INT, c INT);

INSERT INTO TABLE2 VALUES(1,2,3);
INSERT INTO TABLE3 VALUES(4,5,6);

DECLARE @VAR VARCHAR(MAX) = 'YES'

IF @VAR = 'YES'
BEGIN
    INSERT INTO TABLE1
    SELECT * FROM TABLE2
END

ELSE
BEGIN
    INSERT INTO TABLE1
    SELECT * FROM TABLE3
END

Answer 2

It does not work like that. But this is

INSERT INTO #TempTable (COL1, COL2, COL3)
SELECT * FROM TABLE
WHERE @VAR = 'YES' OR REGDATE <= '2015-06-12'