multiple update in IF statement within stored procedure

i have this stored proc and its not letting me have 2 update statements under IF and ELSE. ITs givng me an error near ELSE. and how can i fix it?
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_UpdateTotals2]
AS
BEGIN
 
SET NOCOUNT ON;
 
Declare @isWeekday  tinyint
Declare @isHoliday  tinyint
 
 
--Find out if today is weekend or holiday (True =1, False =0)
set @isWeekday =(select isWeekday  from Corp_calendar T where dt = CONVERT(VARCHAR(10),GETDATE(),101))  
set @isHoliday =  (select isHoliday  from Corp_calendar T where dt = CONVERT(VARCHAR(10),GETDATE(),101)) 
 
 
 --If its weekday, then do the following
IF @isWeekday=1 or @isHoliday =0 
 
 	UPDATE TB_Cummulative 
	SET TB_Cummulative.Cummulative_actual = D.ActualTotal, TB_Cummulative.Cummulative_target = D.TargetTotal
	FROM Auto_Daily_Total D 
	WHERE TB_Cummulative.Run_Date = convert(varchar(10),getdate(),101)  and TB_Cummulative.Board_ID = D.Board_ID
 
   UPDATE TB_Cummulative 
	SET TotalActual = D.TotalActual, TotalTarget = D.TotalTarget 
	FROM TB_Cummulative T 
    INNER JOIN Auto_Monthly_Total D ON T.Board_ID = D.Board_ID
    WHERE T.Run_Date = convert(datetime,CONVERT(VARCHAR(10),GETDATE(),101) ,101) AND D.MONTH=MONTH(GETDATE()) AND D.YEAR = YEAR(GETDATE())
 
ELSE
 
	UPDATE TB_Cummulative 
	SET 
			TB_Cummulative.Cummulative_actual = D.ActualTotal, 
			TB_Cummulative.Cummulative_target = 0
			FROM Auto_Daily_Total D
	WHERE 
			TB_Cummulative.Run_Date = convert(varchar(10),getdate(),101)  and 
			TB_Cummulative.Board_ID = D.Board_ID
 
	 UPDATE TB_Cummulative 
        SET 
                TotalActual = D.TotalActual, 
                TotalTarget = D.TotalTarget - T.Cummulative_Target
                FROM TB_Cummulative T 
                INNER JOIN Auto_Monthly_Total D ON T.Board_ID = D.Board_ID
        WHERE 
                T.Run_Date = convert(datetime,CONVERT(VARCHAR(10),GETDATE(),101) ,101)
				AND D.MONTH=MONTH(GETDATE()) 
                AND D.YEAR = YEAR(GETDATE())
	 
END

Open in new window

sassy168Asked:
Who is Participating?
 
jorgesv13Connect With a Mentor Commented:
You have to do the same between ELSE and END
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_UpdateTotals2]
AS
BEGIN
 
SET NOCOUNT ON;
 
Declare @isWeekday  tinyint
Declare @isHoliday  tinyint
 
 
--Find out if today is weekend or holiday (True =1, False =0)
set @isWeekday =(select isWeekday  from Corp_calendar T where dt = CONVERT(VARCHAR(10),GETDATE(),101))  
set @isHoliday =  (select isHoliday  from Corp_calendar T where dt = CONVERT(VARCHAR(10),GETDATE(),101)) 
 
 
 --If its weekday, then do the following
IF @isWeekday=1 or @isHoliday =0 
 BEGIN
        UPDATE TB_Cummulative 
        SET TB_Cummulative.Cummulative_actual = D.ActualTotal, TB_Cummulative.Cummulative_target = D.TargetTotal
        FROM Auto_Daily_Total D 
        WHERE TB_Cummulative.Run_Date = convert(varchar(10),getdate(),101)  and TB_Cummulative.Board_ID = D.Board_ID
 
   UPDATE TB_Cummulative 
        SET TotalActual = D.TotalActual, TotalTarget = D.TotalTarget 
        FROM TB_Cummulative T 
    INNER JOIN Auto_Monthly_Total D ON T.Board_ID = D.Board_ID
    WHERE T.Run_Date = convert(datetime,CONVERT(VARCHAR(10),GETDATE(),101) ,101) AND D.MONTH=MONTH(GETDATE()) AND D.YEAR = YEAR(GETDATE())
 END
ELSE
 BEGIN
        UPDATE TB_Cummulative 
        SET 
                        TB_Cummulative.Cummulative_actual = D.ActualTotal, 
                        TB_Cummulative.Cummulative_target = 0
                        FROM Auto_Daily_Total D
        WHERE 
                        TB_Cummulative.Run_Date = convert(varchar(10),getdate(),101)  and 
                        TB_Cummulative.Board_ID = D.Board_ID
 
         UPDATE TB_Cummulative 
        SET 
                TotalActual = D.TotalActual, 
                TotalTarget = D.TotalTarget - T.Cummulative_Target
                FROM TB_Cummulative T 
                INNER JOIN Auto_Monthly_Total D ON T.Board_ID = D.Board_ID
        WHERE 
                T.Run_Date = convert(datetime,CONVERT(VARCHAR(10),GETDATE(),101) ,101)
                                AND D.MONTH=MONTH(GETDATE()) 
                AND D.YEAR = YEAR(GETDATE())
  END       
END

Open in new window

0
 
jorgesv13Commented:
Between IF and ELSE insert:
BEGIN
.....
END
You have to use this keywords when you use multiple statements
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.