?
Solved

multiple update in IF statement within stored procedure

Posted on 2008-11-03
2
Medium Priority
?
709 Views
Last Modified: 2012-05-05
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

0
Comment
Question by:sassy168
  • 2
2 Comments
 
LVL 8

Expert Comment

by:jorgesv13
ID: 22872842
Between IF and ELSE insert:
BEGIN
.....
END
You have to use this keywords when you use multiple statements
0
 
LVL 8

Accepted Solution

by:
jorgesv13 earned 2000 total points
ID: 22872850
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question