locked
IF ELSE in sql not working RRS feed

  • Question

  • User-1506965535 posted

    I want to write IF ELSE in SQL but it is not working and giving me error as

    Msg 102, Level 15, State 1, Line 4
    Incorrect syntax near 'ELSE'.

    		 IF 
    				   Emp_doj < convert(datetime,'01/07/' + (2016 - 1), 103) and (Comp_mkey <> 126 or Comp_mkey <> 127 or Comp_mkey <> 128 or Comp_mkey <> 129) 
    				   Insert into p_leave_allocation_14122015 (OL_FirstHalf) values ('1')
    		 ELSE

    what is the error 

    Tuesday, December 15, 2015 12:33 PM

Answers

  • User753101303 posted

    So it is a variable that is prefixed with @. It would be :

    IF @Emp_Doj<CAST(@LYears-1)+'0701'

    Ah! @Emp_Doj should be a date not a varchar. Try to always use the more accurate type. If this is  date the 'YYYYMMDD' string I created will be implicitely converted to a date and the conversion would work. Currently you are comparing strings.

    And if @Emp_Doj is a  date but you are using 01/07/YYYY then the actual date dépends on SQL Server settings (ie it could be July first or January 7) while YYYY0701 is always July first regardless of SQL Server settings.

    BTW GO is to separate a batch of statements and should be deleted as well from your stored procedure.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 16, 2015 10:08 AM

All replies

  • User452040443 posted

    Try something like this:

    IF (Emp_doj < '20150701') -- and (Comp_mkey <> 126 or Comp_mkey <> 127 or Comp_mkey <> 128 or Comp_mkey <> 129) 
        Insert into p_leave_allocation_14122015 (OL_FirstHalf) values ('1');

    Hope this helps.

    Tuesday, December 15, 2015 1:05 PM
  • User753101303 posted

    Hi,

    You can't test values that seems to come out of thin air. Either they are variables (@name) or else they would have to come from a table. It's a bit hard to help as I'm not sure what is your intent. Assuming you want to insert in a table based on finding a row in some other table it would be rather something such as :

     IF EXISTS(SELECT 1 FROM SomeTable WHERE  Emp_doj < convert(datetime,'01/07/' + (2016 - 1), 103) and (Comp_mkey <> 126 or Comp_mkey <> 127 or Comp_mkey <> 128 or Comp_mkey <> 129))
        Insert into p_leave_allocation_14122015 (OL_FirstHalf) values ('1')
    ELSE etc...

    Tuesday, December 15, 2015 1:11 PM
  • User364663285 posted

    I want to write IF ELSE in SQL but it is not working and giving me error as

    Msg 102, Level 15, State 1, Line 4
    Incorrect syntax near 'ELSE'.

    		 IF 
    				   Emp_doj < convert(datetime,'01/07/' + (2016 - 1), 103) and (Comp_mkey <> 126 or Comp_mkey <> 127 or Comp_mkey <> 128 or Comp_mkey <> 129) 
    				   Insert into p_leave_allocation_14122015 (OL_FirstHalf) values ('1')
    		 ELSE

    what is the error 

    See this example

    USE AdventureWorks2008R2;
    GO
    IF OBJECT_ID ('dbo.T1', 'U') IS NOT NULL
        INSERT INTO dbo.T1 (column_4) 
        VALUES ('Explicit value');
    GO
    



    Tuesday, December 15, 2015 1:27 PM
  • User-1506965535 posted

    Hi wmec,

    What I want is

    If Emp_Doj is less than 01/07/CurrentYear - 1 then

    I want to insert into the table p_leave_allocation_14122015 for one column as 1 

    but I 2 get error as 

    Msg 102, Level 15, State 1, Procedure AnnualLeaveAllocation2016, Line 77
    Incorrect syntax near '@EmpCardNo'.
    Msg 137, Level 15, State 2, Line 1
    Must declare the scalar variable "@LYears".
    Msg 156, Level 15, State 1, Line 5
    Incorrect syntax near the keyword 'close'.

    Below is SP

    ALTER PROCEDURE [dbo].[AnnualLeaveAllocation2016]
    @OLType varchar(3),
    @Year int
    AS
         BEGIN
    	   INSERT INTO p_leave_allocation_14122015(Mkey,Entry_Sr_no,Emp_doj, Comp_mkey,FDepartment_Id,
    	    FModule_Id,Month,Year, Emp_mkey, Emp_card_no, U_Datetime, Delete_Flag,Month12,PL_Days,
    	    OL_Days,PL_Days_Opening,Month1,Month2,Month3,Month4,Month5,Month6,Month7,
    		Month8,Month9,Month10,Month11,Total_Alloc,OL_Month1,OL_Month2,OL_Month3,OL_Month4,
    		OL_Month5,OL_Month6,OL_Month7,OL_Month8,OL_Month9,OL_Month10,OL_Month11,OL_Month12,
    		OL_Alloc,PLUtil_Month1,PLUtil_Month2,PLUtil_Month3,PLUtil_Month4,PLUtil_Month5,
    		PLUtil_Month6,PLUtil_Month7,PLUtil_Month8,PLUtil_Month9,PLUtil_Month10,
    		PLUtil_Month11,PLUtil_Month12,OL_FirstHalf,OL_SecHalf,OLUtil_FirstHalf,
    		OLUtil_SecHalf,CO_Days_Opening,COUtil_Month1,COUtil_Month2,COUtil_Month3,
    		COUtil_Month4,COUtil_Month5,COUtil_Month6,COUtil_Month7,COUtil_Month8,COUtil_Month9,
    		COUtil_Month10,COUtil_Month11,COUtil_Month12,Actual_PL,Actual_OL,User_Id,
    		PL_Others_SP,NextLeve_Year,LOPRD_Year, Remarks)
    		SELECT ROW_NUMBER() OVER(ORDER BY lev.MKey) + 1643
    		,ROW_NUMBER() OVER(ORDER BY Entry_Sr_no) + 898, emp.Date_Of_Joining, emp.Comp_mkey,FDepartment_Id, FModule_Id,1,
    		@Year ,Emp_mkey,lev.Emp_card_no,lev.U_Datetime,lev.Delete_Flag,Month12,
    		0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
    		0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,@Year + 1,0, 'New Executive 2016'
    		FROM p_leave_allocation lev LEFT JOIN Emp_mst emp
    		ON Emp.Emp_card_no = lev.Emp_card_no and lev.Year = (@Year -1)
    		WHERE emp.Status in ('A', 'S')  and 
    		YEAR(emp.Date_Of_Joining) <= @Year
    		BEGIN
    			declare @EmpCardNo varchar(20), @Emp_Doj varchar(20), @LYears varchar(10)
    			declare  @pl1 varchar(10),@pl2 varchar(10), @pl3 varchar(10),@pl4 varchar(10), @pl5 varchar(10)  
    			declare @pl6 varchar(10), @pl7 varchar(10),@pl8 varchar(10), @pl9 varchar(10),@pl10 varchar(10)  
    			declare @pl11 varchar(10),@pl12 varchar(10),@plTotal varchar(10),@NextLeaveYear numeric (5,0)
    
    			DECLARE EmpData_Cursor CURSOR FOR 
    				SELECT  Emp_card_no,Emp_doj, Year,
    				Month1, Month2,Month3,Month4,Month5,Month6,Month7,Month8,Month9,Month10,Month11,Month12,
    				Total_Alloc, NextLeve_Year
    				from p_leave_allocation_14122015 
    		
    				open EmpData_Cursor
    				Fetch Next from EmpData_Cursor  into @EmpCardNo,@Emp_Doj,@LYears,@pl1,@pl2,@pl3,@pl4,
    				@pl5,@pl6,@pl7,@pl8,@pl9,@pl10,@pl11,@pl12,@plTotal,@NextLeaveYear
    				WHILE @@FETCH_STATUS = 0
    				
    				BEGIN
    		
    				--print @EmpCardNo
    				fetch next from EmpData_Cursor into @EmpCardNo,@Emp_Doj,@LYears,@pl1,@pl2,@pl3,@pl4,@pl5,
    				@pl6,@pl7,@pl8,@pl9,@pl10,@pl11,@pl12,@plTotal,@NextLeaveYear  
    			   
    				begin    
    				   declare @remaingPL as numeric(10,2)   
    				   set @remaingPL=0  
    				   select @remaingPL= (isnull(PL_Days,0.00)+isnull(PL_Days_Opening,0.00)) - 
    				   (isnull(a.OL_Month1,0.00)+isnull(a.OL_Month2,0.00)+
    				   isnull(a.OL_Month3,0.00)+isnull(a.OL_Month4,0.00)+
    				   isnull(a.OL_Month5,0.00)+isnull(a.OL_Month6,0.00)+
    				   isnull(a.OL_Month7,0.00)+isnull(a.OL_Month8,0.00)+
    				   isnull(a.OL_Month9,0.00)+isnull(a.OL_Month10,0.00)+
    				   isnull(a.OL_Month11,0.00)+isnull(a.OL_Month12,0.00)) 
    				   from p_leave_allocation a where  a.Year = cast(@LYears as numeric)- 1 and a.Emp_card_no = @EmpCardNo  
    				   print  @remaingPL  
    
    				  ----- Remaining PL for less than 0 -----------------------
    				   if(@remaingPL<0)  
    					  begin  
    						 set @remaingPL=0  
    					  end   
    					  
    				   print 'remainingpl'  
    				   print @remaingPL  
    				      update p_leave_allocation_14122015 set PL_Days_Opening =  isnull(@remaingPL,0.00) where Emp_Card_No = @EmpCardNo	  
    			GO
    				IF (Emp_Doj < '01/07/'+ @LYears - 1)
    					INSERT INTO p_leave_allocation_14122015 (OL_FirstHalf) 
    					VALUES ('1')
                                                   GO
    			   
    		END
        close EmpData_Cursor 
       	deallocate EmpData_Cursor 
       	END
       	 
     END
    		 Select a.emp_card_no, a.PL_Days_Opening, b.emp_card_no,
    		 (isnull(b.PL_Days,0.00)+isnull(b.PL_Days_Opening,0.00)) - 
    				   (isnull(b.OL_Month1,0.00)+isnull(b.OL_Month2,0.00)+
    				   isnull(b.OL_Month3,0.00)+isnull(b.OL_Month4,0.00)+
    				   isnull(b.OL_Month5,0.00)+isnull(b.OL_Month6,0.00)+
    				   isnull(b.OL_Month7,0.00)+isnull(b.OL_Month8,0.00)+
    				   isnull(b.OL_Month9,0.00)+isnull(b.OL_Month10,0.00)+
    				   isnull(b.OL_Month11,0.00)+isnull(b.OL_Month12,0.00)) Closing
    		 from p_leave_allocation_14122015 a join p_leave_allocation b ON b.Emp_card_no = a.Emp_card_no and b.Year = (2015)
    		 
             --  truncate table p_leave_allocation_14122015
         --    exec AnnualLeaveAllocation2016 OL, 2016
         
         
         

    Wednesday, December 16, 2015 5:18 AM
  • User753101303 posted

    So it is a variable that is prefixed with @. It would be :

    IF @Emp_Doj<CAST(@LYears-1)+'0701'

    Ah! @Emp_Doj should be a date not a varchar. Try to always use the more accurate type. If this is  date the 'YYYYMMDD' string I created will be implicitely converted to a date and the conversion would work. Currently you are comparing strings.

    And if @Emp_Doj is a  date but you are using 01/07/YYYY then the actual date dépends on SQL Server settings (ie it could be July first or January 7) while YYYY0701 is always July first regardless of SQL Server settings.

    BTW GO is to separate a batch of statements and should be deleted as well from your stored procedure.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 16, 2015 10:08 AM