IF ELSE in sql not working

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 

Answer 1

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.

Answer 2

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...

Answer 3

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

Answer 4

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
     
     
     

Answer 5

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.