While writing stetaments in SQL we have scenarios where manipulations based on conditions needs to be performed

For doing that we use conditional statements

ALTER proc [dbo].[sp_CheckLeaveAvailability]		
@FromDate date,
@ToDate date,
@EmployeeID int,
@GetAvailablePL decimal,
@GetAvailableCL decimal,
@GetAvailableRH int ,
@LeaveReason varchar(200),
@LeaveType int


as

begin

Declare @TotalHolidays int ,@FinalLeaveDuration decimal,@CheckLeaveBalance int,@LeaveStatus varchar(20) ,@Duration decimal,@PaternityAvail binary = 0

set @Duration=(SELECT DATEDIFF(day,@FromDate,@ToDate));

set @TotalHolidays=(select count(*) from EmployeeHolidayDetails where EmployeeHolidayDetailDate between @FromDate and @ToDate ) 



if(@TotalHolidays>0)

begin

if(@TotalHolidays<=@Duration)
begin
set @FinalLeaveDuration=@Duration-@TotalHolidays
end

else
begin
set @FinalLeaveDuration=@TotalHolidays-@Duration
end


end




else

begin
set @FinalLeaveDuration=@Duration
end







if(@LeaveType=1)

begin


if(@FinalLeaveDuration<=@GetAvailablePL)

begin

set @LeaveStatus='Granted'


end


else

begin
set @LeaveStatus='Rejected'
end



end






if(@LeaveType=2)

begin


if(@FinalLeaveDuration<=@GetAvailableCL)

begin
set @LeaveStatus='Granted'
end


else

begin
set @LeaveStatus='Rejected'
end

end







if(@LeaveType=3)

begin

if(@FinalLeaveDuration<=@GetAvailableRH)

begin
set @LeaveStatus='Granted'
end


else

begin
set @LeaveStatus='Rejected'
end



end









if(@LeaveType=4)

begin
set @PaternityAvail=(select count(*) from dbo.EmployeeLeaveDetails where EmployeeLeaveId=@LeaveType and EmployeeId=@EmployeeId) 

if(@FinalLeaveDuration<=7 and @PaternityAvail=0)

begin
set @LeaveStatus='Granted'

end


else

begin
set @LeaveStatus='Rejected'
end



end







select @LeaveStatus as 'LeaveStatus',@TotalHolidays,@FinalLeaveDuration


end

In this stored procedure we are manipulating user leave approval based on conditions