Using an insert statement in an if ... then (SQL)

Question

(Using SQL Server 2008R2) I've got 2 values in a record in a table that will never contain data at the same time. I want to insert a record into 1 of 2 junction tables depending on which field has data.

SELECT @val1 = [propVal1ID]
        ,@val2 = [propVal2ID]
FROM [proposalPackage] 
WHERE [proposalPackageID] = @proposal

IF @val1 > 0
    INSERT INTO [jTable1]
                ([projectID]
                ,[value1_ID]
                ,[subValue1_ID]
                ,[priority]
                ,[isActive])
            SELECT
                @project as projectID
                ,@val1 as [value1_ID]
                ,[subValue1ID] as [subValue1_ID]
                ,1 as [priority]
                ,'true' as [isActive]
            FROM [proposalPackage]
            WHERE [proposalPackageID] = @proposal
ELSE IF @val2 > 0
    INSERT INTO [jTable2]
                ([projectID]
                ,[value2_ID]
                ,[priority]
                ,[isActive])
            SELECT
                @project as projectID
                ,@val2 as [value2_ID]
                ,1 as [priority]
                ,'true' as [isActive]
            FROM [proposalPackage]
            WHERE [proposalPackageID] = @proposal
END IF;

Everything seems to parse until it gets to the "end if;" when it gives me a "syntax error."

I tried a CASE statement but that failed miserably... CASE only seems appropriate when checking the same variable/field value.

Question 1: What is the proper syntax following the end/end if? Everywhere I look, it says to use a semi-colon (;). Question 2: Is there a better way to catch this mouse?

Any assistance is appreciated.

Thanks, Bob

Show us the error message. Also, just a reminder that 0 does not equal NULL — Kamil Gosciminski
– Kamil Gosciminski, Commented Jun 29, 2018 at 14:32
SQL Server doesn't have END IF. It has statement blocks delimited by BEGIN/END. — Gordon Linoff
– Gordon Linoff, Commented Jun 29, 2018 at 14:32
Error message: Msg 102, Level 15, State 1, Line 43 Incorrect syntax near 'END' Also, values are set to 0 in DECLARE statement... — R Loomas
– R Loomas, Commented Jun 29, 2018 at 14:33
@Gordon Linoff ... that was too easy... thanks. How do I make your comment my answer? — R Loomas
– R Loomas, Commented Jun 29, 2018 at 14:37

DavidG · Accepted Answer · 2018-06-29 14:34:25Z

4

SQL Server doesn't use END IF to denote the end of an IF block, you need to wrap the content inside a BEGIN/END:

IF @val1 > 0
BEGIN
    INSERT ... --something
END
ELSE IF @val2 > 0
BEGIN
    INSERT ... --something else
END

Alternatively, since you only have a single statement inside your IF blocks, you can omit the BEGIN/END completely, but I'm not fond of that as it can lead to difficult to spot bugs.

answered Jun 29, 2018 at 14:34

DavidG

120k13 gold badges232 silver badges239 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using an insert statement in an if ... then (SQL)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related