If else statement in insert query

Question

I am facing a problem regarding syntax error in this code snippet

INSERT INTO tblScheduledBillGenerate(SettingConnectionId, UserId, MonthlyBill, BillGenerateDate)
VALUES ( @Id, @UserId, 
                        (   if exists (SELECT *
                            FROM tblServiceBillPackage_Audit WHERE UserId=4 and ServiceBillPackageId=(24) 
                            and Status=1 nd AuditType=N'UPDATE' ORDER BY tblServiceBillPackage_Audit.TimeStamp DESC)

BEGIN 

SELECT 1
                            
END
ELSE 
BEGIN
SELECT 2
END ), @daytoday)

Msg 156, Level 15, State 1, Procedure USP_SCHEDULER_TestBillSchedule, Line 50
Incorrect syntax near the keyword 'if'.
Msg 102, Level 15, State 1, Procedure USP_SCHEDULER_TestBillSchedule, Line 76
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Procedure USP_SCHEDULER_TestBillSchedule, Line 103
Incorrect syntax near 'END'.

The problematic part of the code is given above.

Please add sample data to your question. It is not clear (to me, at least) what you are trying to accomplish here. — Tim Biegeleisen, May 06 '21 at 04:05
I want to insert 1 in that space according to a certain condition else 2 i dont know why im getting a syntax error — M_O_MEN, May 06 '21 at 04:07

score 1 · Answer 1 · answered May 06 '21 at 04:13

You may want to use a CASE in your statement instead. IF can be used in procedures, triggers, and functions, but not inline.

INSERT INTO tblScheduledBillGenerate(SettingConnectionId, UserId, MonthlyBill, BillGenerateDate)
SELECT @Id as SettingConnectionId,
       @UserId as UserId, 
       CASE WHEN (SELECT COUNT(Id)
                    FROM tblServiceBillPackage_Audit
                   WHERE UserId=4 and ServiceBillPackageId=(24) 
                     and Status=1 and AuditType=N'UPDATE') > 0
            THEN 1
            ELSE 2 END as MonthlyBill,
       @daytoday as BillGenerateDate;

This should do what you're looking for.

If else statement in insert query

1 Answers1