CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
R Loomas
R Loomas

Reputation: 333

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

(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

Upvotes: 1

Views: 57

Answers (1)

DavidG
DavidG

Reputation: 118947

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.

Upvotes: 4

Related Questions